使用T-SQL解析URL参数

CREATE TABLE Urls (
    Id INT PRIMARY KEY,
    Url NVARCHAR(MAX)
);

INSERT INTO Urls (Id, Url) VALUES
(1, 'http://example.com/page?param1=value1&param2=value2'),
(2, 'http://example.com/page?param1=value3&param3=value4');

WITH ParsedUrls AS (
    SELECT 
        Id,
        Url,
        -- 提取 '?' 后的部分
        SUBSTRING(Url, CHARINDEX('?', Url) + 1, LEN(Url)) AS QueryString
    FROM 
        Urls
    WHERE 
        CHARINDEX('?', Url) > 0
),
KeyValuePairs AS (
    SELECT 
        Id,
        QueryString,
        -- 将查询字符串按 '&' 分割成多行
        value AS KeyValue
    FROM 
        ParsedUrls
    CROSS APPLY STRING_SPLIT(QueryString, '&')
),
ParsedParams AS (
    SELECT 
        Id,
        KeyValue,
        -- 将每个键值对按 '=' 分割
        LEFT(KeyValue, CHARINDEX('=', KeyValue) - 1) AS ParamName,
        RIGHT(KeyValue, LEN(KeyValue) - CHARINDEX('=', KeyValue)) AS ParamValue
    FROM 
        KeyValuePairs
    WHERE 
        CHARINDEX('=', KeyValue) > 0
)
SELECT 
    Id,
    ParamName,
    ParamValue
FROM 
    ParsedParams
WHERE 
    ParamName = 'param1';