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
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.
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:
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 :-/
Any ideas appreciated.