Print
All Forums Database
LewG007 2 posts Joined 08/14
28 Jan 2015
REGEXP_INSPR works on litteral data, but not in SELECT

Trying to use REGEXP_INSPR to identify if a VARCHAR(50) column contains data that can be converted to Decimal(8,2) and if so convert it.  Below is my SELECT:

SELECT 
       CAST(
        (CASE WHEN REGEXP_INSTR(Trim(Transaction_Amount),'^[+-]?[0-9]+\.? [0-9]*$') > 0 
              THEN Trim(Transaction_Amount)
              ELSE '0' 
         END) AS DECIMAL(8,2)) as TRANS_AMT
 FROM SSP.MYTABLE
 ;

Even though the column does contain only valid formatted decimal data within the VARCHAR, I get back 0.00 for all columns returned.
If I test my REGEXP_INSTR with various litterals, it works like a champ...can't figure out why it is not working when pulling data from the Table :-/

SELECT CAST
      ((CASE WHEN REGEXP_INSTR(Trim('  -112,345.67   '), '^[+-]?[0-9]+\.?[0-9]*$') > 0
          THEN '0'
          ELSE Trim('  -112,345.67   ')         
        END) AS DECIMAL(8,2)) as TRANS_AMT;

TRANS_AMT
-112345.67

Any ideas appreciated.

Tags:
LewG007 2 posts Joined 08/14
30 Jan 2015

Seems the problem was a hidden Control Characters in the VARCHAR(50).  Here is the modified version for any interested parties.  I'm guessing there is probably an easier way to deal with this scenario.

SELECT 
     Transaction_Amount as Raw_TA
    ,Length(Transaction_Amount) as Len_Raw_TA
    ,Length(Trim(Transaction_Amount)) as Len_Trim_TA
    ,Length(oreplace(oreplace(Transaction_Amount,',',''),' ','')) as Len_Orap_TA
    ,CAST(
       (CASE WHEN REGEXP_INSTR(oreplace(oreplace(oreplace(Transaction_Amount,'0D'XC,''),',',''),' ',''),'^[+-]?[0-9]+\.?[0-9]*$') > 0 
           THEN (oreplace(oreplace(oreplace(Transaction_Amount,'0D'XC,''),',',''),' ',''))
           ELSE '0'
        END) AS DECIMAL(8,2)) as TRANS_AMT
FROM SSP.MYTABLE

 

dnoeth 4628 posts Joined 11/04
31 Jan 2015

The oREPLACEs only replace a single character, so you better use oTRANSLATE instead:

(oreplace(oreplace(oreplace(Transaction_Amount,'0D'XC,''),',',''),' ',''))
--> (otranslate(Transaction_Amount,'0D'XC||', ',''))

And when you switch to REGEXP_SUBSTR you can get rid of the CASE, too. When there's no match a NULL is returned.

CAST(COALESCE(REGEXP_SUBSTR(OTRANSLATE(Transaction_Amount,'0D'XC||', ',''),'^[+-]?[0-9]+\.?[0-9]*$'), '0') AS DECIMAL(8,2)) AS TRANS_AMT

If there are no embedded blanks within the decimal string you can remove the OTRANSLATE, too, the typecast skips embedded commas:

CAST(COALESCE(REGEXP_SUBSTR(Transaction_Amount,'[+-]?[0-9,]+\.?[0-9,]*') , '0') AS DECIMAL(8,2)) AS TRANS_AMT

 

Dieter

You must sign in to leave a comment.