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.
Author |
Topic |
shetty_vikas
Starting Member
5 Posts |
Posted - 2012-11-19 : 00:52:57
|
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=141in 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
Master Smack Fu Yak Hacker
1007 Posts |
Posted - 2012-11-19 : 01:14:42
|
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 2008MCTS - Database Development SQL SERVER 2008 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-11-19 : 01:41:28
|
will brandid always come as first querystring parameter? if yes, last suggestion will work fine.otherwise use thisSELECT LEFT(STUFF(@var,1,patindex('%brandid=%')+8,''),CHARINDEX('&',STUFF(@var,1,patindex('%brandid=%')+8,'')+ '&')-1)------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
shetty_vikas
Starting Member
5 Posts |
Posted - 2012-11-19 : 02:02:27
|
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)) |
|
|
shetty_vikas
Starting Member
5 Posts |
Posted - 2012-11-19 : 02:05:33
|
Hi Visakh,your query throws an error : The patindex function requires 2 argument(s). could you please verify |
|
|
senthil_nagore
Master Smack Fu Yak Hacker
1007 Posts |
Posted - 2012-11-19 : 02:09:54
|
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 2008MCTS - Database Development SQL SERVER 2008 |
|
|
senthil_nagore
Master Smack Fu Yak Hacker
1007 Posts |
Posted - 2012-11-19 : 02:15:35
|
quote: Originally posted by visakh16 will brandid always come as first querystring parameter? if yes, last suggestion will work fine.otherwise use thisSELECT LEFT(STUFF(@var,1,patindex('%brandid=%')+8,''),CHARINDEX('&',STUFF(@var,1,patindex('%brandid=%')+8,'')+ '&')-1)------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/
Slightly modifiedSELECT 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 2008MCTS - Database Development SQL SERVER 2008 |
|
|
shetty_vikas
Starting Member
5 Posts |
Posted - 2012-11-19 : 02:16:05
|
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. :( |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-11-19 : 02:25:30
|
SELECT LEFT(STUFF(@var,1,patindex('%brandid=%',@Var)+8,''),CHARINDEX('&',STUFF(@var,1,patindex('%brandid=%',@var)+8,'')+ '&')-1)------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
shetty_vikas
Starting Member
5 Posts |
Posted - 2012-11-19 : 05:00:29
|
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 |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-11-19 : 07:40:12
|
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 |
|
|
|
|
|
|
|