Parse Querystring In Sql Server 2005

Is there a simple way to parse QueryString parameters (e.g. foo=bar&temp=baz) in SQL Server? What I need in the end is a 'table' with the name/value pairs. | foo | bar | | tem

Solution 1:

So to parse the query string, just use a CTE in a function. Here is the code.

CREATEFUNCTION dbo.SplitQueryString (@svarchar(8000))
RETURNStableASRETURN (
    WITH splitter_cte AS (
      SELECT CHARINDEX('&', @s) as pos, 0as lastPos
      UNIONALLSELECT CHARINDEX('&', @s, pos +1), pos
      FROM splitter_cte
      WHERE pos >0
      ),
    pair_cte AS (
    SELECT chunk,
           CHARINDEX('=', chunk) as pos
    FROM (
        SELECTSUBSTRING(@s, lastPos +1,
                         casewhen pos =0then80000else pos - lastPos -1end) as chunk
        FROM splitter_cte) as t1
  )
    SELECTsubstring(chunk, 0, pos) as keyName,
           substring(chunk, pos+1, 8000) as keyValue
    FROM pair_cte
)
GO

declare@queryStringvarchar(2048)
set@queryString='foo=bar&temp=baz&key=value';
SELECT*FROM dbo.SplitQueryString(@queryString)
OPTION(MAXRECURSION 0);

when run produces the following output.

keyName  keyValue
-------  --------
foo      bar
temp     baz
key      value
(3row(s) affected)

I believe that this will do exactly what you are asking.