SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Parsing help
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

craigwg
Posting Yak Master

USA
154 Posts

Posted - 05/07/2013 :  16:53:37  Show Profile  Reply with Quote
I have a column called "page_url" It contains this:

http://74.7.11.79/search/srpcache?ei=UTF-8&p=chicken+little+2005+return+to+oz&xa=jHNvX2blkMjnRYv_BmgF4A--,1367680599&fr=yfp-t-900-s&u=http://cc.bingj.com/cache.aspx?q=chicken+little+2005+return+to+oz&d=4648654572028943&mkt=en-US&setlang=en-US&w=RZCO6PF

My goal is to extract this:

chicken+little+2005+return+to+oz

Of course the &p= is never at the same place and the text in question is never the same length. This was my first approach:

select
CASE
WHEN page_url LIKE '%&p=%' THEN SUBSTR(page_url,INDEX('&p',page_url),4) --4 because I don't know how much text to grab
END
from my_table

What is the syntax for this? Nested substrings and char indexes. These always kill me!

Thanks


Craig Greenwood

waterduck
Aged Yak Warrior

Malaysia
961 Posts

Posted - 05/07/2013 :  22:17:23  Show Profile  Reply with Quote

declare @str varchar(200) = 'http://74.7.11.79/search/srpcache?ei=UTF-8&p=chicken+little+2005+return+to+oz&xa=jHNvX2blkMjnRYv_BmgF4A--,1367680599&fr=yfp-t-900-s&u=http://cc.bingj.com/cache.aspx?q=chicken+little+2005+return+to+oz&d=4648654572028943&mkt=en-US&setlang=en-US&w=RZCO6PF'

select substring(@str, patindex('%&p=%', @str) + 3, patindex('%=%', SUBSTRING(@str, patindex('%&p=%', @str) + 3, len(@str))) - 1)

wait for better answer
Go to Top of Page

russell
Pyro-ma-ni-yak

USA
5072 Posts

Posted - 05/07/2013 :  22:51:43  Show Profile  Visit russell's Homepage  Reply with Quote
If there is always going to be another parameter after &p in the query string, then this will work

declare @str varchar(200) = 'http://74.7.11.79/search/srpcache?ei=UTF-8&p=chicken+little+2005+return+to+oz&xa=jHNvX2blkMjnRYv_BmgF4A--,1367680599&fr=yfp-t-900-s&u=http://cc.bingj.com/cache.aspx?q=chicken+little+2005+return+to+oz&d=4648654572028943&mkt=en-US&setlang=en-US&w=RZCO6PF'

SELECT	SUBSTRING(@str,
		CHARINDEX('&p=', @str) + 3,
		CHARINDEX('&', @str, CHARINDEX('&p=', @str)+1) -
		CHARINDEX('&p=', @str) - 3
)
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 05/08/2013 :  00:17:46  Show Profile  Reply with Quote

SELECT LEFT(STUFF(page_url,1,PATINDEX('%&p=%',page_url)+3,''),CHARINDEX('&',STUFF(page_url,1,PATINDEX('%&p=%',page_url)+3,'')+'&')-1)
FROM table


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

craigwg
Posting Yak Master

USA
154 Posts

Posted - 05/08/2013 :  15:13:00  Show Profile  Reply with Quote
Thanks all. I'm using Teradata so the functions are a touch different. I got it working using this:

CASE
WHEN page_url LIKE '%&q=%' THEN OREPLACE(SUBSTR (page_url,POSITION('&' IN page_url)+3,(POSITION('&' IN SUBSTR(page_url,POSITION('&' IN page_url)+3,1000)))+POSITION('&' IN page_url) -(POSITION('&' IN page_url)+1)),'+',' ')
END

Craig Greenwood
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 05/09/2013 :  00:21:15  Show Profile  Reply with Quote
cool
for Teradata specific helps in future, you may be better off posting questions in Teradata related forums
This is MS SQL Server forums and we deal with Transact SQL

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

russell
Pyro-ma-ni-yak

USA
5072 Posts

Posted - 05/09/2013 :  01:05:31  Show Profile  Visit russell's Homepage  Reply with Quote
^^ yep.

Very good though that you got it working
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000