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
 SQL Server 2008 Forums
 Transact-SQL (2008)
 extract string between two different patterns
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

shetty_vikas
Starting Member

India
5 Posts

Posted - 11/19/2012 :  00:52:57  Show Profile  Reply with Quote
Hi Team ,

I am looking for a Function or Script which could extract a string placed between two different patterns.

Example : http://accessories.dell.com/sna/brand.aspx?brandid=56&c=ca&l=en&s=biz&cs=calca1&p=141

in the above URL, i want to extract string placed between '?brandid=' and the immediate next '&' i.e the output should be '56' .

I should be able to define the patterns between which the string is to be extracted.

senthil_nagore
Flowing Fount of Yak Knowledge

India
1007 Posts

Posted - 11/19/2012 :  01:14:42  Show Profile  Visit senthil_nagore's Homepage  Send senthil_nagore a Yahoo! Message  Reply with Quote
Declare @var varchar(512)

Set @var ='http://accessories.dell.com/sna/brand.aspx?brandid=56&c=ca&l=en&s=biz&cs=calca1&p=141'

SELECT
SUBSTRING(@var, charindex('=',@var)+1,((charindex('&',@var)-charindex('=',@var))-1))



Senthil Kumar C
------------------------------------------------------
MCITP - Database Administration SQL SERVER 2008
MCTS - Database Development SQL SERVER 2008
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 11/19/2012 :  01:41:28  Show Profile  Reply with Quote
will brandid always come as first querystring parameter? if yes, last suggestion will work fine.

otherwise use this

SELECT LEFT(STUFF(@var,1,patindex('%brandid=%')+8,''),CHARINDEX('&',STUFF(@var,1,patindex('%brandid=%')+8,'')+ '&')-1)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

shetty_vikas
Starting Member

India
5 Posts

Posted - 11/19/2012 :  02:02:27  Show Profile  Reply with Quote
thankz for the speedy reply senthil,

Appreciate it.

i guess i would need to use this :

SUBSTRING(@var, charindex('?brandid=',@var)+9,((charindex('&',@var)-charindex('?brandid=',@var))-9))
Go to Top of Page

shetty_vikas
Starting Member

India
5 Posts

Posted - 11/19/2012 :  02:05:33  Show Profile  Reply with Quote
Hi Visakh,

your query throws an error : The patindex function requires 2 argument(s).
could you please verify
Go to Top of Page

senthil_nagore
Flowing Fount of Yak Knowledge

India
1007 Posts

Posted - 11/19/2012 :  02:09:54  Show Profile  Visit senthil_nagore's Homepage  Send senthil_nagore a Yahoo! Message  Reply with Quote
quote:
Originally posted by shetty_vikas

thankz for the speedy reply senthil,

Appreciate it.

i guess i would need to use this :

SUBSTRING(@var, charindex('?brandid=',@var)+9,((charindex('&',@var)-charindex('?brandid=',@var))-9))



Welcome.
If the format is same, both will result same.

Senthil Kumar C
------------------------------------------------------
MCITP - Database Administration SQL SERVER 2008
MCTS - Database Development SQL SERVER 2008
Go to Top of Page

senthil_nagore
Flowing Fount of Yak Knowledge

India
1007 Posts

Posted - 11/19/2012 :  02:15:35  Show Profile  Visit senthil_nagore's Homepage  Send senthil_nagore a Yahoo! Message  Reply with Quote
quote:
Originally posted by visakh16

will brandid always come as first querystring parameter? if yes, last suggestion will work fine.

otherwise use this

SELECT LEFT(STUFF(@var,1,patindex('%brandid=%')+8,''),CHARINDEX('&',STUFF(@var,1,patindex('%brandid=%')+8,'')+ '&')-1)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/






Slightly modified

SELECT LEFT(STUFF(@var,1,patindex('%brandid=%',@var)+7,''),CHARINDEX('&',STUFF(@var,1,patindex('%brandid=%',@var)+7,'')+ '&')-1)


Senthil Kumar C
------------------------------------------------------
MCITP - Database Administration SQL SERVER 2008
MCTS - Database Development SQL SERVER 2008
Go to Top of Page

shetty_vikas
Starting Member

India
5 Posts

Posted - 11/19/2012 :  02:16:05  Show Profile  Reply with Quote
Hi Senthil,

Sorry to bother you , what if 'Brandid' is present after the 1st '&' , i guess the query need to be changed ?? could you please help !!!

example the string is 'http://accessories.dell.com/sna/brand.aspx?c=ca&l=en&brandid=56&s=biz&cs=calca1&p=141'

how to extact 56 from the string then. :(

Edited by - shetty_vikas on 11/19/2012 04:59:13
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 11/19/2012 :  02:25:30  Show Profile  Reply with Quote
SELECT LEFT(STUFF(@var,1,patindex('%brandid=%',@Var)+8,''),CHARINDEX('&',STUFF(@var,1,patindex('%brandid=%',@var)+8,'')+ '&')-1)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

shetty_vikas
Starting Member

India
5 Posts

Posted - 11/19/2012 :  05:00:29  Show Profile  Reply with Quote
quote:
Originally posted by shetty_vikas


Sorry to bother you , what if 'Brandid' is present after the 1st '&' , i guess the query need to be changed ?? could you please help !!!

example the string is 'http://accessories.dell.com/sna/brand.aspx?c=ca&l=en&brandid=56&s=biz&cs=calca1&p=141'

how to extact 56 from the string then. :(



could some1 help me out how to solve the above Scenario
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 11/19/2012 :  07:40:12  Show Profile  Reply with Quote
quote:
Originally posted by shetty_vikas

quote:
Originally posted by shetty_vikas


Sorry to bother you , what if 'Brandid' is present after the 1st '&' , i guess the query need to be changed ?? could you please help !!!

example the string is 'http://accessories.dell.com/sna/brand.aspx?c=ca&l=en&brandid=56&s=biz&cs=calca1&p=141'

how to extact 56 from the string then. :(



could some1 help me out how to solve the above Scenario

From what I can tell code Senthil posted works correctly in that case as well:
DECLARE @var VARCHAR(255) = 'http://accessories.dell.com/sna/brand.aspx?c=ca&l=en&brandid=56&s=biz&cs=calca1&p=141' ;
SELECT LEFT(STUFF(@var,1,patindex('%brandid=%',@var)+7,''),CHARINDEX('&',STUFF(@var,1,patindex('%brandid=%',@var)+7,'')+ '&')-1)

-- result
-- 56
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.12 seconds. Powered By: Snitz Forums 2000