Parse URL parameters using T-SQL

by 
--CREATE FUNCTION [dbo].[ufn_ParseQueryString] ( @QueryString AS VARCHAR(MAX) )  
DECLARE @QueryString AS VARCHAR(MAX) = 'pubid=mdfbgd&utm_source=facebook&utm_medium=digimun&placement=Instagram_Stories'
DECLARE @QueryStringTable TABLE ( [Key] VARCHAR(100), [Value] VARCHAR(1000) ) 

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

SELECT * FROM @QueryStringTable

--RETURN

Leave a Comment