SQL中如何将&分隔的长参数字符串解析为多个新列

原有写法问题排查 #

你当前的写法存在三个核心问题：

• 仅匹配带&前缀的program=，如果该参数处于字符串首位就会匹配失败
• 没有限定截取的结束位置，会返回当前参数之后的所有剩余内容
• 未处理URL编码格式，得到的结果会存在%2F、%20这类转义字符

最优实现方案（适配SQL Server） #

核心思路是先给原参数字符串统一拼接前缀&，让所有参数都符合&参数名=的格式，匹配到参数起始位置后，取下一个&的位置作为结束边界截取内容，最后通过XML方式完成URL解码。

通用单参数提取模板 #

-- 提取非末尾参数
CAST('<a>' + REPLACE(SUBSTRING(
    '&' + Parameters,
    CHARINDEX('&参数名=', '&' + Parameters) + LEN('&参数名='),
    CHARINDEX('&', '&' + Parameters, CHARINDEX('&参数名=', '&' + Parameters) + LEN('&参数名=')) - (CHARINDEX('&参数名=', '&' + Parameters) + LEN('&参数名='))
), '%', '&#x') + '</a>' AS XML).value('.', '返回值类型') AS 别名

针对末尾没有&的参数，需要加CASE判断处理结束位置：

-- 提取末尾参数
CAST('<a>' + REPLACE(SUBSTRING(
    '&' + Parameters,
    CHARINDEX('&参数名=', '&' + Parameters) + LEN('&参数名='),
    CASE WHEN CHARINDEX('&', '&' + Parameters, CHARINDEX('&参数名=', '&' + Parameters) + LEN('&参数名=')) = 0
         THEN LEN('&' + Parameters)
         ELSE CHARINDEX('&', '&' + Parameters, CHARINDEX('&参数名=', '&' + Parameters) + LEN('&参数名='))
    END - (CHARINDEX('&参数名=', '&' + Parameters) + LEN('&参数名='))
), '%', '&#x') + '</a>' AS XML).value('.', '返回值类型') AS 别名

完整6参数提取查询 #

SELECT
  -- 提取StartDate
  CAST('<a>' + REPLACE(SUBSTRING(
    '&' + Parameters,
    CHARINDEX('&StartDate=', '&' + Parameters) + 10,
    CHARINDEX('&', '&' + Parameters, CHARINDEX('&StartDate=', '&' + Parameters) + 10) - (CHARINDEX('&StartDate=', '&' + Parameters) + 10)
  ), '%', '&#x') + '</a>' AS XML).value('.', 'DATETIME') AS StartDate,
  -- 提取EndDate
  CAST('<a>' + REPLACE(SUBSTRING(
    '&' + Parameters,
    CHARINDEX('&EndDate=', '&' + Parameters) + 8,
    CHARINDEX('&', '&' + Parameters, CHARINDEX('&EndDate=', '&' + Parameters) + 8) - (CHARINDEX('&EndDate=', '&' + Parameters) + 8)
  ), '%', '&#x') + '</a>' AS XML).value('.', 'DATETIME') AS EndDate,
  -- 提取Program
  CAST('<a>' + REPLACE(SUBSTRING(
    '&' + Parameters,
    CHARINDEX('&Program=', '&' + Parameters) + 9,
    CHARINDEX('&', '&' + Parameters, CHARINDEX('&Program=', '&' + Parameters) + 9) - (CHARINDEX('&Program=', '&' + Parameters) + 9)
  ), '%', '&#x') + '</a>' AS XML).value('.', 'VARCHAR(100)') AS Program,
  -- 提取Location
  CAST('<a>' + REPLACE(SUBSTRING(
    '&' + Parameters,
    CHARINDEX('&Location=', '&' + Parameters) + 10,
    CHARINDEX('&', '&' + Parameters, CHARINDEX('&Location=', '&' + Parameters) + 10) - (CHARINDEX('&Location=', '&' + Parameters) + 10)
  ), '%', '&#x') + '</a>' AS XML).value('.', 'VARCHAR(200)') AS Location,
  -- 提取Status
  CAST('<a>' + REPLACE(SUBSTRING(
    '&' + Parameters,
    CHARINDEX('&Status=', '&' + Parameters) + 8,
    CHARINDEX('&', '&' + Parameters, CHARINDEX('&Status=', '&' + Parameters) + 8) - (CHARINDEX('&Status=', '&' + Parameters) + 8)
  ), '%', '&#x') + '</a>' AS XML).value('.', 'VARCHAR(50)') AS Status,
  -- 提取末尾参数Name
  CAST('<a>' + REPLACE(SUBSTRING(
    '&' + Parameters,
    CHARINDEX('&Name=', '&' + Parameters) + 6,
    CASE WHEN CHARINDEX('&', '&' + Parameters, CHARINDEX('&Name=', '&' + Parameters) + 6) = 0
         THEN LEN('&' + Parameters)
         ELSE CHARINDEX('&', '&' + Parameters, CHARINDEX('&Name=', '&' + Parameters) + 6)
    END - (CHARINDEX('&Name=', '&' + Parameters) + 6)
  ), '%', '&#x') + '</a>' AS XML).value('.', 'VARCHAR(100)') AS Name
FROM 你的日志表名

注意事项 #

• 如果参数名大小写不固定，可以把CHARINDEX的两个参数都转为大写/小写匹配，例如CHARINDEX(UPPER('&Program='), UPPER('&' + Parameters))
• 其他数据库适配思路一致，仅需要替换对应字符串函数即可：MySQL用LOCATE代替CHARINDEX，PostgreSQL用STRPOS代替CHARINDEX，且自带url_decode函数可直接解码

内容的提问来源于stack exchange，提问作者Jarrod