Regex string works on single string but not on column

Database
Fan

Regex string works on single string but not on column

Hi,

 

I'm trying to extract either a 9,8 or 7 digit sequence of numbers from a varchar column that can take many different forms, to do this I have written the following regex_replace, see the below example of a 9 digit sequence in an example bunch of text: 

 

SyntaxEditor Code Snippet

regexp_replace('869946442 test text :102 ','[^0-9]+[^0-9]{9}[0-9]+\s+','' )

returns:
869946442

 In this instance the regex_replace works and I get the result I want, the 9 digit sequence of numbers at the beginning. 

 

However, when I call the regex_replace on a column, it does nothing, and returns the original value:

regexp_replace(col1,'[^0-9]+[^0-9]{9}[0-9]+\s+','' )
returns:
869946442 test text :102

Does anyone know why this behaviour might occur? 

3 REPLIES
Senior Apprentice

Re: Regex string works on single string but not on column

Hi,

Take teh 'col1' out of quotes, it should just be col1.

 

Cheers,

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Enthusiast

Re: Regex string works on single string but not on column

REGEXP_SUBSTR(col, '[0-9]{7,9}')

Regexp_substr is better in this case
Enthusiast

Re: Regex string works on single string but not on column

Assuming your column always starts with the digit secuence that you want to extract. This should work.

SyntaxEditor Code Snippet

SELECT      regexp_replace(TRIM(col1),'[^0-9]{7,9}.*','') FROM STAGEP.ALEXPRUEBAS

 Notice me if you need a different kind of regex.

 

 

EDIT:

 

SyntaxEditor Code Snippet

    SELECT      REGEXP_SUBSTR(col1, '[0-9]{7,9}') FROM STAGEP.ALEXPRUEBAS

 Works better as  ProteusIQ said.