extract string between two different patterns

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

SQL Server 2008 Forums

Transact-SQL (2008)

extract string between two different patterns

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=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
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 2008
MCTS - 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 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

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 2008
MCTS - 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 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

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 MVP
http://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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources