Parsing help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Parsing help

Author

Topic

craigwg
Posting Yak Master

154 Posts

Posted - 2013-05-07 : 16:53:37

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

982 Posts

Posted - 2013-05-07 : 22:17:23

[code]
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)
[/code]
wait for better answer

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2013-05-07 : 22:51:43

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
)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-08 : 00:17:46

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

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

craigwg
Posting Yak Master

154 Posts

Posted - 2013-05-08 : 15:13:00

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-09 : 00:21:15

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

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2013-05-09 : 01:05:31

^^ yep.

Very good though that you got it working

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources