Using a CTE in a Function to Split Up a Query String

The question came up as to how do I parse a query string using TSQL.  So here you go, using a common table expression, and basing this on a similar function to what I put together yesterday for split.

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);

which produces this output.

A quick and easy way to parse a query string  in TSQL using a CTE.

Posted in CTE Tagged with: , , , , ,

Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

Powered by sweet Captcha

Newsletter Signup

Stay informed of Database Corruption Challenge events, and other things happening at SteveStedman.com with my newsletter.
Newsletter signup form.

Stedman Solutions

StedmanSolutionsRemoteSkilledDBA

Common Table Expressions Book

Paperback:

Kindle: