SQL Server 14中简洁拆分含特殊分隔符列至多列的方法

针对你的需求，这里提供一个无需多次REPLACE和STRING_SPLIT嵌套的单查询方案，直接通过字符定位截取实现：

SELECT
    ID,
    weirdstring,
    -- 提取第一组
    SUBSTRING(weirdstring, 1, COALESCE(NULLIF(CHARINDEX(';', weirdstring), 0), 
                                      NULLIF(CHARINDEX('+', weirdstring), 0), 
                                      NULLIF(CHARINDEX('-', weirdstring), 0), 
                                      LEN(weirdstring))) AS group1,
    -- 提取第二组
    CASE 
        WHEN first_delim_pos IS NOT NULL THEN
            SUBSTRING(weirdstring, first_delim_pos + 1, 
                      COALESCE(NULLIF(CHARINDEX(';', weirdstring, first_delim_pos + 1), 0), 
                               NULLIF(CHARINDEX('+', weirdstring, first_delim_pos + 1), 0), 
                               NULLIF(CHARINDEX('-', weirdstring, first_delim_pos + 1), 0), 
                               LEN(weirdstring) + 1) - first_delim_pos - 1)
        ELSE NULL
    END AS group2,
    -- 提取第三组
    CASE 
        WHEN second_delim_pos IS NOT NULL THEN
            SUBSTRING(weirdstring, second_delim_pos + 1, 
                      COALESCE(NULLIF(CHARINDEX(';', weirdstring, second_delim_pos + 1), 0), 
                               NULLIF(CHARINDEX('+', weirdstring, second_delim_pos + 1), 0), 
                               NULLIF(CHARINDEX('-', weirdstring, second_delim_pos + 1), 0), 
                               LEN(weirdstring) + 1) - second_delim_pos - 1)
        ELSE NULL
    END AS group3
FROM (
    SELECT 
        ID,
        weirdstring,
        -- 定位第一个分隔符位置
        COALESCE(NULLIF(CHARINDEX(';', weirdstring), 0), 
                 NULLIF(CHARINDEX('+', weirdstring), 0), 
                 NULLIF(CHARINDEX('-', weirdstring), 0)) AS first_delim_pos,
        -- 定位第二个分隔符位置
        COALESCE(NULLIF(CHARINDEX(';', weirdstring, first_delim_pos + 1), 0), 
                 NULLIF(CHARINDEX('+', weirdstring, first_delim_pos + 1), 0), 
                 NULLIF(CHARINDEX('-', weirdstring, first_delim_pos + 1), 0)) AS second_delim_pos
    FROM TESTTABLE
) AS sub

方案说明 #

1. 分隔符定位：用CHARINDEX依次查找;、+、-的位置，NULLIF把找不到时返回的0转为NULL，COALESCE按顺序取第一个存在的分隔符位置。
2. 分组截取：
   • group1：从字符串开头截到第一个分隔符，没有分隔符就取整串。
   • group2：如果存在第一个分隔符，从它的下一位开始截到第二个分隔符；没有第二个分隔符就取剩余全部内容。
   • group3：逻辑和group2一致，基于第二个分隔符的位置截取第三组。
3. 灵活调整：如果需要改变分隔符的优先级，直接调整COALESCE里的CHARINDEX顺序即可。

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