All Forums Database
sunnysteve 13 posts Joined 10/13
21 Apr 2016
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

dnoeth 4628 posts Joined 11/04
21 Apr 2016

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

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

 

Dieter

sunnysteve 13 posts Joined 10/13
21 Apr 2016

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.!

dnoeth 4628 posts Joined 11/04
21 Apr 2016

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

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

 

Dieter

sunnysteve 13 posts Joined 10/13
21 Apr 2016

Thanks a lot Dieter for the help.
 
Is there a documentation for the REGEXP_SUBSTR In teradata ??

You must sign in to leave a comment.