All Forums Database
22 Jul 2015
help needed in a regular expression

Hi Gurus,
Hope you are in good health!!
I am stuck using a regular expression and need your valuable inputs or helps.
sample data in the column
column-site_data_text

U=g17293|sWAS-ORL|pair

U=g17297|sMLB-NYC|pair

U=g17293|sWAS-MLB|pair

 

from the above data I need to extract only 17293(numeric part). There are occurences where data is as below as well.

 

U=gc16036x157|sSJC-LAX|paph

U=gc16036x157|sJFK-TLV|paph

U=gc16036x157|sMCO-KIN|paph

 

in the above example the output would be 16036x157

 

This is a migration from a different DB(Greenplum). Expression in Greenplum is as below which is working.

 

LOWER(SUBSTRING(site_data_text FROM E'[U=|]g(.+?)[|$]')) 

 

Thanks

 

 

 

 

 

 

dnoeth 4628 posts Joined 11/04
22 Jul 2015

To extract the numeric part you can use this regex:

regexp_substr(site_data_text, '([0-9x]+)')

 

Dieter

23 Jul 2015

this is not only numeric extract, there are records starting with "U=z" "U=c" etc. So I need to write a regular expression where it will only fetch the numeric records for U=g.

23 Jul 2015

Logic i want is if the string is "U=g17293|sWAS-ORL|pair" then my regular expression should return me data between "U=g" amd the first pipe which is  17293

CarlosAL 512 posts Joined 04/08
23 Jul 2015

Hi.
 
SELECT REGEXP_SUBSTR(TheColumn,'(?<=U\=g)[0-9]*')
 
HTH
 
Cheers.
 
Carlos.

You must sign in to leave a comment.