Query String Parser Function

A query string is the part of a URL that contains the data to be passed from the web browser and sent to the web application for processing. In a URL, the query string follows a separating character, usually a question mark (?). The query string is composed of a series of key-value pairs. Within each pair, the key and value are separated by an equals sign (=). The series of key-value pairs are typically separated by the ampersand (&) character.

Here’s a function that parses a query string and returns the key-value pairs as a table:

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 here’s a sample query that calls this function and outputs the query string key-value pairs as a table:

SELECT * FROM [dbo].[ufn_ParseQueryString] ( 'fname=Mickey&lname=Mouse&addr=1313+Disneyland+Dr&city=Anaheim&st=CA&zip=92802' )

Key     Value
------- ----------------------------
fname   Mickey
lname   Mouse
addr    1313+Disneyland+Dr
city    Anaheim
st      CA
zip     92802