CODEWITHSUNDEEP
teradatateradata-sql-assistant
Abhishek Kumar Singh
Abhishek Kumar Singh

Reputation: 15

substring operation or regex in teradata

I have data as below

col1
abc1234
abc 1234
12345
abc 1234 123456789
xyz1234567890a

I want output having the string which is numeric with length >=5 characters, rest all records filtered.

I have tried function REGEXP_SUBSTR(col1, '[0-9]+'), but it is not giving desired result

SELECT  col1 
,REGEXP_SUBSTR(col1, '[0-9]+') as num
FROM table1 
WHERE col1 IS NOT NULL 
AND LENGTH(num) >5

expected output is as below 

num
12345
123456789
1234567890

Upvotes: 0

Views: 151

Answers (1)

dnoeth
dnoeth

Reputation: 60472

You need tell the RegEx to return at least five consecutive digits, currently it's at least one digit. And of course, if you want >= 5 you shouldn't write > 5 :-)

RegExp_Substr(col1, '[0-9]{5,}')

Upvotes: 1

Related Questions