SQL按特殊字符拆分单列数据为多列的查询编写求助

原有SQL存在的问题 #

• 未处理无分隔符场景：当tags不含>时，CHARINDEX返回0，SUBSTRING取负长度会直接报错
• 分隔符使用错误：计算中间字段长度时误用-作为匹配字符，和实际拆分规则完全不符
• 缺少合法性校验：未按要求判断Deptno、Empno是否为数字，也没有处理空值场景

正确SQL语句 #

WITH pre_calc AS (
    SELECT
        tags,
        -- 第一个>的位置，无分隔符返回0
        CHARINDEX('>', tags) AS first_sep_idx,
        -- 最后一个>的位置，无分隔符返回0
        CASE WHEN CHARINDEX('>', tags) > 0 
             THEN LEN(tags) - CHARINDEX('>', REVERSE(tags)) + 1 
             ELSE 0 END AS last_sep_idx,
        -- 统计>的总数量
        LEN(tags) - LEN(REPLACE(tags, '>', '')) AS sep_count
    FROM ods.testtable
)
SELECT
    tags,
    -- 提取Deptno：仅分隔符左侧为数字时返回，否则为NULL
    CASE WHEN sep_count >= 1 AND ISNUMERIC(LEFT(tags, first_sep_idx - 1)) = 1
         THEN LEFT(tags, first_sep_idx - 1)
         ELSE NULL END AS Deptno,
    -- 提取empname
    CASE 
        WHEN sep_count = 0 THEN tags
        WHEN sep_count >= 1 THEN NULLIF(SUBSTRING(tags, first_sep_idx + 1, last_sep_idx - first_sep_idx - 1), '')
        ELSE NULL END AS empname,
    -- 提取Empno：仅分隔符右侧为数字时返回，否则为NULL
    CASE WHEN sep_count >= 2 AND ISNUMERIC(RIGHT(tags, LEN(tags) - last_sep_idx)) = 1
         THEN RIGHT(tags, LEN(tags) - last_sep_idx)
         ELSE NULL END AS Empno
FROM pre_calc

覆盖场景验证 #

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