Search code examples
teradatateradata-sql-assistant

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
Solution

  • 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,}')