SQL Substring variable text within URL string

Question

I'm trying to parse information from a column of URLS in SQL Server 2012

The basic structure is

http://www.domainsomething.com/?test/campaign=abc&kwd=jdc ftp&catgory=brnd

Where I need to grab the parameters from the kwd= value. In the above example, it would be the 'jdc ftp', but this would might be different for all the URLs in my column. I've tried CHARINDEX and LEN, but I can't seem to put the syntax together correctly.

This is really not something you want to be doing in SQL Server. In a good design the client application could be anything, not just a web application. You should not have to go down to the guts of HTML in your SQL code - separate it out into the parameters you expect and let the calling code sort it out. — LoztInSpace
– LoztInSpace, Commented Jun 28, 2015 at 11:05

Zohar Peled · Accepted Answer · 2015-06-28 10:36:19Z

1

Try this:

DECLARE @URL varchar(100) = 'http://www.domainsomething.com/?test/campaign=abc&kwd=jdc ftp&catgory=brnd'

SELECT SUBSTRING(
       @URL, 
       CHARINDEX('kwd=', @URL)+4, 
       CASE WHEN CHARINDEX('&', @URL, CHARINDEX('kwd=', @URL)+5) > 0 THEN
       CHARINDEX('&', @URL, CHARINDEX('kwd=', @URL)+5)
        - (CHARINDEX('kwd=', @URL)+4)
       ELSE
          LEN(@URL)
       END
       )

This will also handle cases when the kwd is the last or first variable in the query string.

You can play with it yourself in this sql fiddle.

edited Jun 28, 2015 at 10:36

answered Jun 28, 2015 at 10:25

Zohar Peled

82.9k11 gold badges79 silver badges134 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

shA.t Over a year ago

I suggest CHARINDEX('kwd=', @URL + 'kwd=')+4 to return '' for a URL that has no kwd= ;)

Zohar Peled Over a year ago

@shA.t That might be a good idea, but it is dependent on business logic. I was going on the assumption that a url without a kwd parameter should raise an error.

Collectives™ on Stack Overflow

SQL Substring variable text within URL string

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related