CODEWITHSUNDEEP
sqlteradata
Grigory P
Grigory P

Reputation: 191

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

Upvotes: 1

Views: 2121

Answers (3)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520948

Use REGEXP_REPLACE:

SELECT
    REGEXP_REPLACE(col, '[A-Za-z0-9]+_([0-9])+_([0-9])+$', '\1,\2', 1, 0) AS output
FROM yourTable;

If you really need bona fide decimals, then you can cast the above output, e.g.

SELECT
    CAST(REGEXP_REPLACE(col, '[A-Za-z0-9]+_([0-9])+_([0-9])+$', '\1.\2', 1, 0)
         AS DECIMAL(10,2)) AS output
FROM yourTable;

Upvotes: 2

dnoeth
dnoeth

Reputation: 60462

Another way using REGEXP_SUBSTR and TO_NUMBER:

 To_Number(RegExp_Substr(col, '([0-9])+_([0-9])+$'), '9999D99', 'NLS_NUMERIC_CHARACTERS = ''_.''')

Upvotes: 4

Bhavesh Ghodasara
Bhavesh Ghodasara

Reputation: 2071

There are multiple ways you can do that.

1) Using substring and instring.

2) Strtok function

strtok(colname, '_', 2)||','||strtok(colname, '_', 3)||'0'

3) Regex function

Upvotes: 0

Related Questions