Parse URL parameters using T-SQL

Question

I have some Url for digital marketing. These URLs have parameters, so I want to parse the URL and insert a table. For follow campaigns and analyze.

For example:

https://www.example.com/?utm_source=google&utm_medium=blabla&utm_campaign=gameuser&utm_term=winwin&utm_content=takego

I want insert table like that. How can I do that?

Cab you create a [split()](https://stackoverflow.com/questions/10581772/how-to-split-a-comma-separated-value-to-columns?page=1&tab=oldest#tab-top) function ? — Thomas G, Mar 01 '19 at 15:56
You really, really don't want to do this in TSQL. You better use a programming language for this, and send the already parsed values to the database. — Zohar Peled, Mar 01 '19 at 16:04

score 2 · Answer 1 · answered Sep 08 '21 at 23:54

You can use a function

CREATE FUNCTION [dbo].[ufn_ParseQueryString] ( @QueryString AS VARCHAR(MAX) )  RETURNS @QueryStringTable TABLE ( [Key] VARCHAR(100), [Value] VARCHAR(1000) ) AS BEGIN
DECLARE @QueryStringPair        VARCHAR(2000)
DECLARE @Key                    VARCHAR(100)
DECLARE @Value                  VARCHAR(1000)

WHILE LEN(@QueryString) > 0
BEGIN
    SET @QueryStringPair = LEFT ( @QueryString, ISNULL(NULLIF(CHARINDEX('&', @QueryString) - 1, -1), 
                                  LEN(@QueryString)))
    SET @QueryString = SUBSTRING( @QueryString, ISNULL(NULLIF(CHARINDEX('&', @QueryString), 0), 
                                  LEN(@QueryString)) + 1, LEN(@QueryString))

    SET @Key   = LEFT (@QueryStringPair, ISNULL(NULLIF(CHARINDEX('=', @QueryStringPair) - 1, -1), 
                       LEN(@QueryStringPair)))
    SET @Value = SUBSTRING( @QueryStringPair, ISNULL(NULLIF(CHARINDEX('=', @QueryStringPair), 0), 
                            LEN(@QueryStringPair)) + 1, LEN(@QueryStringPair))

    INSERT INTO @QueryStringTable ( [Key], [Value] )
    VALUES ( @Key, @Value )
END

RETURN

END

and test

SELECT * FROM [dbo].[ufn_ParseQueryString] ( 'pubid=mdfbgd&utm_source=facebook&utm_medium=digimun&placement=Instagram_Stories' )

score 1 · Answer 2 · answered Mar 01 '19 at 16:23

Ideally you will do this in a stored procedure so that you can break the work into bite-sized chunks. You can do it all in 1 SQL statement but it becomes a bit of a nightmare as you get to the last few parameters!

The basic approach is to use a combination of CHARINDEX & SUBSTRING to parse the URL... You find the first ? then take from there to the first & (then split this either side of the =) Then you find the next & (split by = ) and rinse & repeat.

I've started the process off here for you - you should be able to extend this to get the rest of your parameters from the URL - Its a bit slow & clunky but at least you can see whats happening:

Run this bit of SQL and you'll soon get the idea:

declare @str VARCHAR(8000)= 'https://www.example.com/?utm_source=google&utm_medium=blabla&utm_campaign=gameuser&utm_term=winwin&utm_content=takego'

DECLARE @str1 VARCHAR(8000)= SUBSTRING(@str, CHARINDEX( '?',@str, 1)+1, CHARINDEX( '&',@str, 1) -CHARINDEX( '?',@str, 1)-1)

SELECT SUBSTRING(@str1, 1, CHARINDEX( '=',@str1, 1)-1)

SELECT SUBSTRING(@str1, CHARINDEX( '=',@str1, 1)+1, LEN(@Str1))

DECLARE @str2  VARCHAR(8000) = SUBSTRING(@str, CHARINDEX( '?',@str, 1) + len(@str1) + 2, LEN(@str))

SELECT @str2 = SUBSTRING(@str2, 1, CHARINDEX( '&',@str2, 1)-1)

SELECT @str2

Thanks John for your answer.Yes I want to make this with a store procedure.Because I have a table and there are many url like that.And every day insert new urls.How can I do that automaticly with a procedure? — Dekareş, Mar 02 '19 at 13:23
to get this approach to work against a table will require some sort of loop. This might not be a big problem if your table size is relatively small and growing slowly. So i'd suggest running some sort of while loop and parse the columns you need then insert them one row at a time within the loop until all done. — john McTighe, Mar 04 '19 at 09:05

Parse URL parameters using T-SQL

2 Answers2