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