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?

Answer 1

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' )

Answer 2

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