Need help in writing a SQL to extract a code from the column which is a varying URL

Database
Enthusiast

Need help in writing a SQL to extract a code from the column which is a varying URL

Hi,

I am having hardtime writing the most eficient SQL for the following use case.

Column(URL)

/extreme/social/Step7.jsp?Source=1234&returnURL=/dyn/dyn/servlet/securePdf.dyn?file=/content/dam/role/PDFs/secure/safetravel.pdf&isCQPage=true

/extreme/social/Step7.jsp?returnURL=/extreme/contest/member/contest.jsp?Source=4565&contest=2740co&Source=4565&_requestid=001235

/extreme/social/Step7.jsp?Source=13564&returnURL=

I need to write an SQL to extract the digit code which is bolded above from a huge dataset .

Any help would be appreciated.

Thanks

Tags (2)
4 REPLIES
Senior Apprentice

Re: Need help in writing a SQL to extract a code from the column which is a varying URL

Use a regular expression, look for the string '&source=' and return the following digits:

REGEXP_SUBSTR(col, '(?<=&source=)\d*',1,1,'i')
Enthusiast

Re: Need help in writing a SQL to extract a code from the column which is a varying URL

Hi Dieter,

Thanks for the solution.

When I apply the above solution I get the following output.

Column                                                                                                                                                                    Output

/common/profile/Step1.jsp?Source=1003&psrc=MN_R6014R21665NW                                                                         ?

/common/profile/Step1.jsp?Source=1245&returnURL=/dyn/dyn/servlet/se                                                                     ?

/common/profile/Step1.jsp?returnURL=/common/contest/member/contest.jsp?Source=8888&contest=42100             8888

It is not getting the digit code for all the URLS for some reason.

Thanks for  you help.!

Senior Apprentice

Re: Need help in writing a SQL to extract a code from the column which is a varying URL

There's a '?' instead of a '&', you might simply remove the first character from the regex:

REGEXP_SUBSTR(col, '(?<=source=)\d*',1,1,'i')
Enthusiast

Re: Need help in writing a SQL to extract a code from the column which is a varying URL

Thanks a lot Dieter for the help.

Is there a documentation for the REGEXP_SUBSTR In teradata ??