| Author |
 Topic  |
|
|
shetty_vikas
Starting Member
India
5 Posts |
 Posted - 11/19/2012 : 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=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
Aged Yak Warrior
India
997 Posts |
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47069 Posts |
Posted - 11/19/2012 : 01:41:28
|
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/
|
 |
|
|
shetty_vikas
Starting Member
India
5 Posts |
Posted - 11/19/2012 : 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
India
5 Posts |
Posted - 11/19/2012 : 02:05:33
|
Hi Visakh,
your query throws an error : The patindex function requires 2 argument(s).
could you please verify |
|
|
|
senthil_nagore
Aged Yak Warrior
India
997 Posts |
Posted - 11/19/2012 : 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 2008
MCTS - Database Development SQL SERVER 2008 |
|
|
|
senthil_nagore
Aged Yak Warrior
India
997 Posts |
Posted - 11/19/2012 : 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 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 |
|
|
|
shetty_vikas
Starting Member
India
5 Posts |
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47069 Posts |
Posted - 11/19/2012 : 02:25:30
|
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/
|
|
|
|
shetty_vikas
Starting Member
India
5 Posts |
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 11/19/2012 : 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
|
|
|
| |
Topic |
|
|
|
extract string between two different patterns
