SQL Server中按规则提取列内字符串至指定列（顺序不一致场景）

针对你遇到的字符串排列顺序不固定导致提取值混乱的问题，核心解决方案是基于内容特征匹配提取，而非依赖位置顺序。以下是具体实现步骤和代码：

分析各字段特征 #

先明确要提取的三类内容的识别规则：

• Name：人名，特征为非代码类字符串（无HCP/CHSP/CVI前缀，通常包含空格）
• Program Type：项目代码，固定为HCP2或CHSP
• CVI Level：CVI等级，特征为以CVI开头的字符串，提取其中的CVI+数字部分

具体SQL实现 #

假设你的表名为YourTable，存储JSON内容的列名为JsonColumn，以下代码可以精准提取对应列：

SELECT
    -- 提取Name：匹配非代码、非CVI的client项
    MAX(CASE 
        WHEN TRIM(SUBSTRING(s.value, CHARINDEX(':', s.value)+1, LEN(s.value))) NOT IN ('HCP2', 'CHSP')
             AND NOT TRIM(SUBSTRING(s.value, CHARINDEX(':', s.value)+1, LEN(s.value))) LIKE 'CVI%'
        THEN TRIM(SUBSTRING(s.value, CHARINDEX(':', s.value)+1, LEN(s.value)))
    END) AS Name,
    -- 提取Program Type：匹配HCP2/CHSP
    MAX(CASE 
        WHEN TRIM(SUBSTRING(s.value, CHARINDEX(':', s.value)+1, LEN(s.value))) IN ('HCP2', 'CHSP')
        THEN TRIM(SUBSTRING(s.value, CHARINDEX(':', s.value)+1, LEN(s.value)))
    END) AS [Program Type],
    -- 提取CVI Level：匹配CVI开头的项，提取CVI+数字部分
    MAX(CASE 
        WHEN TRIM(SUBSTRING(s.value, CHARINDEX(':', s.value)+1, LEN(s.value))) LIKE 'CVI%'
        THEN LEFT(TRIM(SUBSTRING(s.value, CHARINDEX(':', s.value)+1, LEN(s.value))), 4)
    END) AS [CVI Level]
FROM YourTable
-- 第一步：从JSON中提取tags字段
CROSS APPLY (SELECT JSON_VALUE(JsonColumn, '$.tags') AS tags) AS t
-- 第二步：拆分tags中的每个client项
CROSS APPLY STRING_SPLIT(t.tags, ',') AS s
-- 过滤空的拆分项（因为tags末尾有逗号）
WHERE TRIM(s.value) != ''
GROUP BY YourTable.guid -- 按guid分组，确保每行对应原数据的一条记录

代码说明 #

1. JSON_VALUE提取tags：先从JSON列中取出tags的字符串内容
2. STRING_SPLIT拆分：把逗号分隔的client:xxx项拆分成独立行
3. 条件聚合MAX()：通过CASE判断每个拆分项的类型，用MAX()把同一行的对应值聚合到目标列（因为分组后每个类型只会有一个匹配值）
4. 过滤空项：排除tags末尾逗号产生的空字符串，避免干扰判断

测试结果 #

针对你提供的示例数据，执行后会得到：

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