SQL Server中如何拆分范围格式的Unitnumber数据？

需求说明 #

需要将包含多种特殊格式（逗号分隔、&分隔、/范围、to范围）的Unitnumber拆分为多行数据，例如：

• #201-12/14 → #201-12、#201-13、#201-14
• #101-34to 37 → #101-34到#101-37的连续编号
• #01-112, #01-K106 & #01-113/114 → 拆分出单个单元+范围展开

实现代码 #

1. 准备测试数据 #

CREATE TABLE #TestData (
    Unitnumber VARCHAR(100),
    Number INT
);

INSERT INTO #TestData VALUES
('#201-12/14', 1),
('#101-34to 37', 2),
('#01-112, #01-K106 & #01-113/114', 4);

2. 核心拆分逻辑 #

WITH SplitUnits AS (
    -- 第一步：拆分多单元（替换&为逗号，再按逗号拆分）
    SELECT 
        TRIM(value) AS UnitPart,
        td.Number
    FROM #TestData td
    CROSS APPLY STRING_SPLIT(REPLACE(td.Unitnumber, '&', ','), ',')
),
Numbers AS (
    -- 生成数字序列（覆盖常规范围需求，可调整上限）
    SELECT 0 AS n
    UNION ALL
    SELECT n + 1 FROM Numbers WHERE n < 1000
),
ProcessedUnits AS (
    -- 提取前缀、后缀，判断单元类型
    SELECT
        UnitPart,
        Number,
        LEFT(UnitPart, CHARINDEX('-', UnitPart) + 1) AS Prefix,
        SUBSTRING(UnitPart, CHARINDEX('-', UnitPart) + 1, LEN(UnitPart)) AS Suffix,
        CASE 
            WHEN CHARINDEX('to', UnitPart) > 0 THEN 'RangeTo'
            WHEN CHARINDEX('/', UnitPart) > 0 THEN 'RangeSlash'
            ELSE 'Single'
        END AS UnitType
    FROM SplitUnits
),
RangeUnits AS (
    -- 处理范围型单元，生成连续编号
    SELECT
        Prefix + CAST(startNum + n AS VARCHAR) AS Unitnumber,
        Number
    FROM (
        -- 解析to格式的范围
        SELECT
            Prefix,
            Number,
            CAST(LEFT(Suffix, CHARINDEX('to', Suffix) - 1) AS INT) AS startNum,
            CAST(LTRIM(SUBSTRING(Suffix, CHARINDEX('to', Suffix) + 2, LEN(Suffix))) AS INT) AS endNum
        FROM ProcessedUnits
        WHERE UnitType = 'RangeTo'
        UNION ALL
        -- 解析/格式的范围
        SELECT
            Prefix,
            Number,
            CAST(LEFT(Suffix, CHARINDEX('/', Suffix) - 1) AS INT) AS startNum,
            CAST(SUBSTRING(Suffix, CHARINDEX('/', Suffix) + 1, LEN(Suffix)) AS INT) AS endNum
        FROM ProcessedUnits
        WHERE UnitType = 'RangeSlash'
    ) r
    JOIN Numbers n ON n.n BETWEEN 0 AND (endNum - startNum)
),
SingleUnits AS (
    -- 保留单个单元（含带字母的特殊编号）
    SELECT
        UnitPart AS Unitnumber,
        Number
    FROM ProcessedUnits
    WHERE UnitType = 'Single'
)
-- 合并结果并排序
SELECT Unitnumber, Number FROM RangeUnits
UNION ALL
SELECT Unitnumber, Number FROM SingleUnits
ORDER BY Number, Unitnumber;

代码逻辑说明 #

1. SplitUnits：将原始字符串中的&替换为逗号，再用STRING_SPLIT拆分出独立单元，同时去除多余空格。
2. Numbers：递归生成数字序列，用于生成范围内的连续编号。
3. ProcessedUnits：提取每个单元的前缀（如#201-）和后缀（如12/14），并判断单元是范围型还是单个型。
4. RangeUnits：解析范围型单元的起始/结束数字，用数字序列生成所有中间编号，拼接前缀得到完整Unitnumber。
5. SingleUnits：直接保留非范围型的单个单元（如#01-K106）。
6. 最后合并两类结果并排序，得到最终拆分数据。

扩展说明 #

• 如果需要保留编号的前导零（如#01-003/005需生成#01-003），可将CAST(startNum + n AS VARCHAR)替换为FORMAT(startNum + n, REPLICATE('0', LEN(LEFT(Suffix, CHARINDEX('/', Suffix) - 1))))。
• 可根据实际需求调整Numbers的数字上限，覆盖更大范围的编号需求。

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