SQL Server Helper - Tip of the Day

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=Barack&lname=Obama&addr=1600 Pennsylvania Ave NW
&city=Washington&st=DC&zip=20500' )

Key     Value
------- ----------------------------
fname   Barack
lname   Obama
addr    1600 Pennsylvania Ave NW
city    Washington
st      DC
zip     20500