Grigory P Grigory P - 11 months ago 139
SQL Question

Teradata 15: extracting number from STRING and putting it into DECIMAL

I need to extract srings like this:

QQ_34_5
HHR_43_9
ET_7_25


into DECIMAL numbers, receiving this:

34,50
43,90
7,25


What is the correct function(s) ?

thx

Answer Source

Another way using REGEXP_SUBSTR and TO_NUMBER:

 To_Number(RegExp_Substr(col, '([0-9])+_([0-9])+$'), '9999D99', 'NLS_NUMERIC_CHARACTERS = ''_.''')
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download