help needed in a regular expression

Database

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


4 REPLIES
Senior Apprentice

Re: help needed in a regular expression

To extract the numeric part you can use this regex:

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

Re: help needed in a regular expression

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.

Re: help needed in a regular expression

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

Junior Supporter

Re: help needed in a regular expression

Hi.

SELECT REGEXP_SUBSTR(TheColumn,'(?<=U\=g)[0-9]*')

HTH

Cheers.

Carlos.