CODEWITHSUNDEEP
sqlteradata
Swa
Swa

Reputation: 13

Extracting numeric value in teradata

I have a column which is varchar data type.Some sample values are like 

abc 56 def
34 ghi
jkl mno 78

I wanted to get the numeric values only, like

56
34
78

Thanks in advance.

Upvotes: 1

Views: 18109

Answers (4)

dnoeth
dnoeth

Reputation: 60462

If you're on TD14 you can simply use a Regular Expression:

REGEXP_SUBSTR(col, '[0-9]+')

Before you might have the OTRANSLATE UDF, there's an old trick to remove any character but a list of wanted ones:

OTRANSLATE(col,OTRANSLATE(col, '0123456789',''),'')

Upvotes: 3

Rob Paller
Rob Paller

Reputation: 7786

If you have Teradata 14 you can use Regular Expressions to find your numeric value and then use substring to extract it. The following SQL assumes there is a single numeric value in your string. However, the parameters of the REGEXP_INSTR are flexible enough that with some tweaking you can find the second or third occurrence given that your numeric value is surround by whitespace. This should get you started in the right direction: 

SELECT REGEXP_INSTR('abc 56 def', '[x0-9]', 1, 1, 0, 'i') AS Start_
     , REGEXP_INSTR('abc 56 def', '[x0-9]', 1, 1, 1, 'i') AS End_
     , SUBSTRING('abc 56 def' FROM Start_ FOR (End_ - Start_ + 1));

Upvotes: 0

user2711819
user2711819

Reputation: 960

SEL OTRANSLATE('abc 56 def', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ!@#$%^&*()', '')

or

column data = 'abc 56 def' '34 ghi' 'jkl mno 78'

SEL columnname,OTRANSLATE(columnname, 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ!@#$%^&*()', '') 
FROM TableA;

Upvotes: 0

DrBailey
DrBailey

Reputation: 183

You could use a substring to do this:

SELECT SUBSTRING(field, PATINDEX('%[0-9]%', field), LEN(field))

Let me know if this helps! (I'm not entirely sure Teradata supports PATINDEX and am unable to test currently).

Edit: Check out http://carlosal.wordpress.com/2012/11/12/only-numbers-en-teradata-ii/ for Teradata specific information from an expert.

Upvotes: 0

Related Questions