如何用SQL Server查询提取字段星号内序列号并拆分展示？

解决方案查询语句 #

SELECT 
    t.ID,
    TRIM(s.value) AS Serial_Numbers
FROM 
    YourTableName t
CROSS APPLY 
    STRING_SPLIT(
        -- 提取两个星号之间的内容
        SUBSTRING(
            t.Description,
            CHARINDEX('*', t.Description) + 1,
            CHARINDEX('*', t.Description, CHARINDEX('*', t.Description) + 1) 
            - CHARINDEX('*', t.Description) 
            - 1
        ),
        ',' -- 按逗号拆分
    ) s
-- 过滤无有效星号对的行
WHERE 
    CHARINDEX('*', t.Description) > 0
    AND CHARINDEX('*', t.Description, CHARINDEX('*', t.Description) + 1) 
        > CHARINDEX('*', t.Description);

语句解释 #

1. 提取星号间内容：

   • CHARINDEX('*', t.Description)：定位第一个星号的位置
   • CHARINDEX('*', t.Description, CHARINDEX('*', t.Description)+1)：从第一个星号之后开始查找第二个星号的位置
   • SUBSTRING(...)：通过两个星号的位置计算截取长度，提取出中间的序列号字符串

2. 拆分序列号：

   • STRING_SPLIT(..., ',')：将截取到的逗号分隔字符串拆分为多行
   • TRIM(s.value)：去除每个序列号前后的空格，确保结果整洁

3. 关联原表ID：

   • CROSS APPLY：将拆分后的每行序列号与原表对应的ID进行关联，实现一对多的展开

4. 过滤无效行：

   • WHERE子句确保仅处理包含有效星号对（至少两个星号且第二个在第一个之后）的记录，避免因格式错误导致的异常

兼容旧版本SQL Server（2016之前） #

如果你的SQL Server版本不支持STRING_SPLIT，可以使用递归CTE结合SUBSTRING实现拆分：

WITH SplitCTE AS (
    SELECT 
        ID,
        -- 初始提取第一个逗号前的内容
        TRIM(SUBSTRING(SerialStr, 1, CHARINDEX(',', SerialStr + ',') - 1)) AS Serial_Numbers,
        -- 剩余未拆分的字符串
        STUFF(SerialStr, 1, CHARINDEX(',', SerialStr + ','), '') AS RemainingStr
    FROM (
        -- 先提取星号间的内容作为待拆分字符串
        SELECT 
            ID,
            SUBSTRING(
                Description,
                CHARINDEX('*', Description) + 1,
                CHARINDEX('*', Description, CHARINDEX('*', Description)+1) 
                - CHARINDEX('*', Description) 
                - 1
            ) AS SerialStr
        FROM YourTableName
        WHERE 
            CHARINDEX('*', Description) > 0
            AND CHARINDEX('*', Description, CHARINDEX('*', Description)+1) 
                > CHARINDEX('*', Description)
    ) t
    WHERE SerialStr <> ''
    UNION ALL
    SELECT 
        ID,
        TRIM(SUBSTRING(RemainingStr, 1, CHARINDEX(',', RemainingStr + ',') - 1)) AS Serial_Numbers,
        STUFF(RemainingStr, 1, CHARINDEX(',', RemainingStr + ','), '') AS RemainingStr
    FROM SplitCTE
    WHERE RemainingStr <> ''
)
SELECT ID, Serial_Numbers
FROM SplitCTE;

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