SQL Server按逗号拆分文本时避免拆分注释字段的技术咨询

你的问题出在全局替换逗号生成JSON数组时，把comment字段内部的逗号也当成了字段分隔符，导致字段错位。要解决这个问题，需要只拆分字段之间的逗号，保留comment内部的逗号。

方案一：精准字符串拆分（推荐） #

利用字符串定位函数，根据固定的字段顺序（ID→Status→comment→Updated_By→Created_By→Date_Created），分别提取每个字段，避开comment内部的逗号：

INSERT INTO xyz(ID, Status, comments, Updated_By, Created_By, Date_Created)
SELECT 
    ID = LEFT(cleaned, CHARINDEX(',', cleaned) - 1),
    Status = TRIM(SUBSTRING(cleaned, CHARINDEX(',', cleaned) + 1, CHARINDEX(',', cleaned, CHARINDEX(',', cleaned) + 1) - CHARINDEX(',', cleaned) - 1)),
    comments = TRIM(SUBSTRING(cleaned, 
                        CHARINDEX(',', cleaned, CHARINDEX(',', cleaned) + 1) + 1, 
                        LEN(cleaned) - CHARINDEX(',', cleaned, CHARINDEX(',', cleaned) + 1) - CHARINDEX(',', REVERSE(cleaned), CHARINDEX(',', REVERSE(cleaned)) + CHARINDEX(',', REVERSE(cleaned), CHARINDEX(',', REVERSE(cleaned)) + 1)) + 1)),
    Updated_By = TRIM(SUBSTRING(cleaned, 
                        LEN(cleaned) - CHARINDEX(',', REVERSE(cleaned), CHARINDEX(',', REVERSE(cleaned)) + CHARINDEX(',', REVERSE(cleaned), CHARINDEX(',', REVERSE(cleaned)) + 1)) + 2, 
                        CHARINDEX(',', REVERSE(cleaned), CHARINDEX(',', REVERSE(cleaned)) + 1) - CHARINDEX(',', REVERSE(cleaned)) - 1)),
    Created_By = TRIM(SUBSTRING(cleaned, 
                        LEN(cleaned) - CHARINDEX(',', REVERSE(cleaned)) + 2, 
                        CHARINDEX(',', REVERSE(cleaned)) - CHARINDEX(',', REVERSE(cleaned), CHARINDEX(',', REVERSE(cleaned)) + 1) - 1)),
    Date_Created = TRIM(REPLACE(RIGHT(cleaned, CHARINDEX(',', REVERSE(cleaned)) - 1), '''', ''))
FROM @dataset
CROSS APPLY (VALUES(REPLACE(REPLACE(records, '(', ''), ')', ''))) AS T(cleaned)

代码说明： #

1. cleaned字段：先去掉原始记录首尾的括号，简化后续处理；
2. ID：取第一个逗号前的内容；
3. Status：取第一个逗号与第二个逗号之间的内容；
4. comments：取第二个逗号之后、倒数第三个逗号之前的内容（避开后面三个字段的分隔符）；
5. Updated_By/Created_By：从字符串末尾反向定位逗号，提取对应字段；
6. Date_Created：取最后一个逗号后的内容，去掉单引号并清理空格。

方案二：改进JSON生成逻辑 #

先将comment内部的逗号替换为临时占位符，生成JSON数组后再替换回逗号，避免全局替换破坏字段内容：

INSERT INTO xyz(ID, Status, comments, Updated_By, Created_By, Date_Created)
SELECT 
    ID = JSON_VALUE(S, '$[0]'),
    Status = JSON_VALUE(S, '$[1]'),
    comments = TRIM(REPLACE(JSON_VALUE(S, '$[2]'), '|||', ',')),
    Updated_By = JSON_VALUE(S, '$[3]'),
    Created_By = JSON_VALUE(S, '$[4]'),
    Date_Created = TRIM(JSON_VALUE(S, '$[5]'))
FROM @dataset
CROSS APPLY (VALUES(REPLACE(REPLACE(records, '(', ''), ')', ''))) AS T(raw_str)
CROSS APPLY (
    VALUES(
        STUFF(raw_str, 
            CHARINDEX(',', raw_str, CHARINDEX(',', raw_str) + 1) + 1,
            LEN(raw_str) - CHARINDEX(',', raw_str, CHARINDEX(',', raw_str) + 1) - CHARINDEX(',', REVERSE(raw_str), CHARINDEX(',', REVERSE(raw_str)) + CHARINDEX(',', REVERSE(raw_str), CHARINDEX(',', REVERSE(raw_str)) + 1)) + 1,
            REPLACE(SUBSTRING(raw_str, 
                CHARINDEX(',', raw_str, CHARINDEX(',', raw_str) + 1) + 1,
                LEN(raw_str) - CHARINDEX(',', raw_str, CHARINDEX(',', raw_str) + 1) - CHARINDEX(',', REVERSE(raw_str), CHARINDEX(',', REVERSE(raw_str)) + CHARINDEX(',', REVERSE(raw_str), CHARINDEX(',', REVERSE(raw_str)) + 1)) + 1),
            ',', '|||')
        )
    ) AS T(processed_str)
CROSS APPLY (VALUES('["' + REPLACE(STRING_ESCAPE(processed_str, 'json'), ',', '","') + '"]')) AS B(S)

代码说明： #

1. 先清理原始字符串的括号；
2. 精准定位comment字段，将其内部的逗号替换为|||（可选用其他不冲突的临时符）；
3. 生成JSON数组时，全局替换字段分隔逗号；
4. 最后将comment字段中的临时符替换回逗号，得到正确内容。

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