在SQL Server 2005中解析QueryString [英] Parse QueryString in SQL Server 2005
问题描述
在SQL Server中是否有一种简单的方法来解析QueryString参数(例如foo = bar& temp = baz)?
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 |
虽然在上面的示例中这很简单,但是如果字符串开始包含转义字符(例如%3D),则变得更加困难,而在涉及UTF-8时,则变得更加困难.
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.
任何现有的解决方案?用于SQL Server的URLDecode的实现将是人间天堂.
Any existing solutions? An implementation of URLDecode for SQL Server would be heaven on earth.
推荐答案
因此,要解析查询字符串,只需在函数中使用CTE.这是代码.
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.
这篇关于在SQL Server 2005中解析QueryString的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!