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.
More from Stedman Solutions:
Steve and the team at Stedman Solutions are here for all your SQL Server needs.
Contact us today for your free 30 minute consultation..
We are ready to help!
Leave a Reply