如何在MS SQL中拆分键值对字符串提取字段生成结构化表

针对给出的固定键: 值,格式字符串，直接用字符串定位截取的方式兼容性最强，支持SQL Server 2008及以上所有版本，不会出现拆分后顺序错乱的问题。

完整可运行代码 #

-- 模拟存储原始字符串的源表，实际使用时替换成自己的表名即可
WITH source_table AS (
    SELECT 'Country: USA, Town: Washington, Street: North Wells Street, Post Code: 60614, ID: 1' AS raw_string
    UNION ALL
    SELECT 'Country: USA, Town: Chicago, Street: 26th Street NW, Post Code: 20037, ID: 2' AS raw_string
)
-- 核心拆分逻辑
SELECT
    LTRIM(SUBSTRING(raw_string,
        CHARINDEX('Country: ', raw_string) + 9,
        CHARINDEX(', Town:', raw_string) - CHARINDEX('Country: ', raw_string) -9
    )) AS Country,
    LTRIM(SUBSTRING(raw_string,
        CHARINDEX('Town: ', raw_string) + 6,
        CHARINDEX(', Street:', raw_string) - CHARINDEX('Town: ', raw_string) -6
    )) AS Town,
    LTRIM(SUBSTRING(raw_string,
        CHARINDEX('Street: ', raw_string) + 8,
        CHARINDEX(', Post Code:', raw_string) - CHARINDEX('Street: ', raw_string) -8
    )) AS Street,
    LTRIM(SUBSTRING(raw_string,
        CHARINDEX('Post Code: ', raw_string) + 11,
        CHARINDEX(', ID:', raw_string) - CHARINDEX('Post Code: ', raw_string) -11
    )) AS [Post Code],
    LTRIM(SUBSTRING(raw_string,
        CHARINDEX('ID: ', raw_string) + 4,
        LEN(raw_string)
    )) AS ID
FROM source_table

运行结果 #

执行后直接返回需要的结构化表：

逻辑说明 #

• 核心用CHARINDEX函数定位每个固定键名的起始下标，再定位下一个键名前的逗号下标，通过SUBSTRING截取两个下标中间的内容
• 外层套LTRIM是为了去掉截取到的值前面多余的空格
• 代码里的偏移量（比如+9、+6）是对应键名字符串的长度，比如Country: 一共9个字符，加这个长度就刚好跳到值的起始位置
• 实际使用时只需要把CTE部分替换成自己存原始字符串的表，把raw_string换成实际存储字符串的字段名即可
• 如果后续键名有增减，对应增减截取段、修改匹配的键名规则就行。如果是SQL Server 2022以上版本，也可以用带enable_ordinal参数的STRING_SPLIT函数拆分后做行转列，但固定键名场景下上面的截取方式性能更高，也没有版本限制。

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