Parse QueryString in SQL Server 2005

Question

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

While it would be simple in the above example it becomes harder if the strings start to contain escaped characters (e.g. %3D) and even tougher when UTF-8 is involved.

Any existing solutions? An implementation of URLDecode for SQL Server would be heaven on earth.

Related thread - http://stackoverflow.com/questions/3833229/sql-server-url-decoding — KV Prajapati, Jan 24 '12 at 11:42
Sounds like something more suited to CLR integration than TSQL. Or can your application not do it before submitting the data? — Martin Smith, Jan 24 '12 at 12:41
I'm dealing with an "ancient" ClassicASP webshop system. I could change the routine writing the data into DB, too - but nobody knows what that would break on some other place... Maybe later... — BlaM, Jan 24 '12 at 12:54

score 7 · Answer 1 · answered Apr 10 '12 at 04:11

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

CREATE FUNCTION dbo.SplitQueryString (@s varchar(8000))
RETURNS table
AS
RETURN (
    WITH splitter_cte AS (
      SELECT CHARINDEX('&', @s) as pos, 0 as lastPos
      UNION ALL
      SELECT CHARINDEX('&', @s, pos + 1), pos
      FROM splitter_cte
      WHERE pos > 0
      ),
    pair_cte AS (
    SELECT chunk,
           CHARINDEX('=', chunk) as pos
    FROM (
        SELECT SUBSTRING(@s, lastPos + 1,
                         case when pos = 0 then 80000
                         else pos - lastPos -1 end) as chunk
        FROM splitter_cte) as t1
  )
    SELECT substring(chunk, 0, pos) as keyName,
           substring(chunk, pos+1, 8000) as keyValue
    FROM pair_cte
)
GO

declare @queryString varchar(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
(3 row(s) affected)

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

Parse QueryString in SQL Server 2005

1 Answers1

Linked