SQL技术问询：如何根据Number列字符串值生成新行？已试CHARINDEX/STUFF未果

这问题我之前处理过，要把带区间（..）和并列（|）的Number列拆成单独的数字行，得分步拆解处理。下面给你一个适配SQL Server的完整解决方案，用递归CTE实现，不需要额外创建辅助表：

完整SQL代码 #

WITH SplitItems AS (
    -- 第一步：把|分隔的所有项转成XML格式，方便后续拆分
    SELECT 
        Row,
        Description,
        CAST('<item>' + REPLACE(Number, '|', '</item><item>') + '</item>' AS XML) AS ItemsXml
    FROM mytable
    WHERE Number IS NOT NULL AND Number <> ''
),
ItemList AS (
    -- 提取每个独立的项（比如1105..1110、2805这类）
    SELECT 
        si.Row,
        si.Description,
        item.value('.', 'VARCHAR(20)') AS Item
    FROM SplitItems si
    CROSS APPLY si.ItemsXml.nodes('item') AS x(item)
),
SplitRanges AS (
    -- 区分单个数字和区间，拆分出区间的起始/结束值
    SELECT 
        Row,
        Description,
        CASE 
            WHEN CHARINDEX('..', Item) > 0 THEN LEFT(Item, CHARINDEX('..', Item)-1)
            ELSE Item
        END AS StartNum,
        CASE 
            WHEN CHARINDEX('..', Item) > 0 THEN RIGHT(Item, LEN(Item) - CHARINDEX('..', Item)-1)
            ELSE Item
        END AS EndNum
    FROM ItemList
),
GenerateNumbers AS (
    -- 递归生成区间内的所有连续数字
    SELECT 
        Row,
        Description,
        CAST(StartNum AS INT) AS Number
    FROM SplitRanges
    UNION ALL
    SELECT 
        Row,
        Description,
        Number + 1
    FROM GenerateNumbers gn
    JOIN SplitRanges sr ON gn.Row = sr.Row AND gn.Description = sr.Description 
        AND gn.Number < CAST(sr.EndNum AS INT)
)
-- 输出最终结果并排序
SELECT 
    Row,
    Description,
    CAST(Number AS VARCHAR(20)) AS Number
FROM GenerateNumbers
ORDER BY Row, Number
OPTION (MAXRECURSION 0); -- 解除递归深度限制，适配大区间

代码分步说明 #

1. SplitItems：把Number列里的|替换成XML标签，把整个字符串转成XML结构，这是SQL里通用的字符串拆分技巧，兼容所有SQL Server版本。
2. ItemList：通过CROSS APPLY和nodes()方法，把XML里的每个<item>节点提取成单独的行，得到所有独立的数字项/区间项。
3. SplitRanges：检查每个项是否包含..，如果是区间就拆分出起始和结束数字；如果是单个数字，起始和结束值相同。
4. GenerateNumbers：用递归CTE生成连续数字——先取每个项的起始数字，然后递归加1直到达到结束数字，自动覆盖单个数字（起始=结束，只会生成一行）和区间的情况。
5. 最后查询结果并排序，得到你需要的格式。

简化版（SQL Server 2016+） #

如果你的SQL Server是2016及以上版本，可以用内置的STRING_SPLIT函数替代XML拆分，代码更简洁：

WITH ItemList AS (
    SELECT 
        Row,
        Description,
        value AS Item
    FROM mytable
    CROSS APPLY STRING_SPLIT(Number, '|')
    WHERE Number IS NOT NULL AND Number <> ''
),
SplitRanges AS (
    SELECT 
        Row,
        Description,
        CASE 
            WHEN CHARINDEX('..', Item) > 0 THEN LEFT(Item, CHARINDEX('..', Item)-1)
            ELSE Item
        END AS StartNum,
        CASE 
            WHEN CHARINDEX('..', Item) > 0 THEN RIGHT(Item, LEN(Item) - CHARINDEX('..', Item)-1)
            ELSE Item
        END AS EndNum
    FROM ItemList
),
GenerateNumbers AS (
    SELECT 
        Row,
        Description,
        CAST(StartNum AS INT) AS Number
    FROM SplitRanges
    UNION ALL
    SELECT 
        Row,
        Description,
        Number + 1
    FROM GenerateNumbers gn
    JOIN SplitRanges sr ON gn.Row = sr.Row AND gn.Description = sr.Description 
        AND gn.Number < CAST(sr.EndNum AS INT)
)
SELECT 
    Row,
    Description,
    CAST(Number AS VARCHAR(20)) AS Number
FROM GenerateNumbers
ORDER BY Row, Number
OPTION (MAXRECURSION 0);

这个方案会自动忽略表中Number为NULL的行，完全匹配你给出的预期输出结果。

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