Microsoft SQL Server提取并展开带连字符数字的技术求助

我用Microsoft SQL Server处理数据时，碰到一列格式如下的数据：

5-7(A-C) 15(A-C)
3(A-C)

需要提取里面的数字：如果数字带连字符（比如5-7），得把连字符两端和中间的所有数字都列出来（第一行要输出5, 6, 7, 15）；单个数字直接提取（第二行输出3）。提取结果要用来关联另一张表的数据。

目前写的SQL只能提取范围的起始数字，拿不到中间的数字，代码如下：

SELECT 
    CASE 
        WHEN CHARINDEX('-', SUBSTRING(cc_EXPRESSION, 1, CHARINDEX('(', cc_EXPRESSION) - 1)) > 0
            THEN CAST(LEFT(SUBSTRING(cc_EXPRESSION, 1, CHARINDEX('(', cc_EXPRESSION) - 1), CHARINDEX('-', SUBSTRING(cc_EXPRESSION, 1, CHARINDEX('(', cc_EXPRESSION) - 1)) - 1) AS INT)
        ELSE CAST(SUBSTRING(cc_EXPRESSION, 1, CHARINDEX('(', cc_EXPRESSION) - 1) AS INT)
    END AS extracted_number

要搞定这个需求，得分三步来：拆分每行里的多个数字条目、提取每个条目的数字/数字范围、把范围展开成所有中间数字。下面是具体实现：

1. 生成数字辅助表（可选） #

如果数据库里没有现成的数字表，用递归CTE生成一个足够大的数字序列就行，比如覆盖0到100（根据你的数据范围调整上限）：

WITH Numbers AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM Numbers WHERE n <= 100
)

2. 拆分字符串并提取数字范围 #

先把每行里用空格分隔的多个条目拆成单独的行，再从每个条目里抠出括号前的数字部分，拆分出范围的起止数字：

WITH Numbers AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM Numbers WHERE n <= 100
),
SplitEntries AS (
    -- 按空格拆分每行的多个条目
    SELECT 
        t.cc_EXPRESSION,
        LTRIM(RTRIM(SUBSTRING(t.cc_EXPRESSION, n, CHARINDEX(' ', t.cc_EXPRESSION + ' ', n) - n))) AS entry
    FROM YourTable t
    JOIN Numbers n ON n.n <= LEN(t.cc_EXPRESSION)
        AND SUBSTRING(' ' + t.cc_EXPRESSION, n, 1) = ' '
),
ExtractRanges AS (
    -- 提取每个条目的数字/范围
    SELECT 
        entry,
        -- 拆分范围起始数字
        CASE 
            WHEN CHARINDEX('-', entry_part) > 0 THEN CAST(LEFT(entry_part, CHARINDEX('-', entry_part) - 1) AS INT)
            ELSE CAST(entry_part AS INT)
        END AS start_num,
        -- 拆分范围结束数字（单个数字的话起止相同）
        CASE 
            WHEN CHARINDEX('-', entry_part) > 0 THEN CAST(RIGHT(entry_part, LEN(entry_part) - CHARINDEX('-', entry_part)) AS INT)
            ELSE CAST(entry_part AS INT)
        END AS end_num
    FROM (
        SELECT 
            entry,
            SUBSTRING(entry, 1, CHARINDEX('(', entry) - 1) AS entry_part
        FROM SplitEntries
        WHERE entry <> '' -- 过滤空条目
    ) AS temp
)
-- 把范围展开成所有连续数字
SELECT DISTINCT n.n AS extracted_number
FROM ExtractRanges er
JOIN Numbers n ON n.n BETWEEN er.start_num AND er.end_num
ORDER BY extracted_number;

3. 合并成逗号分隔的字符串（可选） #

要是需要把同一行的结果合并成逗号分隔的字符串（比如第一行输出5,6,7,15），用SQL Server 2017及以上支持的STRING_AGG函数就行：

WITH Numbers AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM Numbers WHERE n <= 100
),
SplitEntries AS (
    SELECT 
        t.cc_EXPRESSION,
        LTRIM(RTRIM(SUBSTRING(t.cc_EXPRESSION, n, CHARINDEX(' ', t.cc_EXPRESSION + ' ', n) - n))) AS entry
    FROM YourTable t
    JOIN Numbers n ON n.n <= LEN(t.cc_EXPRESSION)
        AND SUBSTRING(' ' + t.cc_EXPRESSION, n, 1) = ' '
),
ExtractRanges AS (
    SELECT 
        t.cc_EXPRESSION,
        CASE 
            WHEN CHARINDEX('-', entry_part) > 0 THEN CAST(LEFT(entry_part, CHARINDEX('-', entry_part) - 1) AS INT)
            ELSE CAST(entry_part AS INT)
        END AS start_num,
        CASE 
            WHEN CHARINDEX('-', entry_part) > 0 THEN CAST(RIGHT(entry_part, LEN(entry_part) - CHARINDEX('-', entry_part)) AS INT)
            ELSE CAST(entry_part AS INT)
        END AS end_num
    FROM (
        SELECT 
            t.cc_EXPRESSION,
            SUBSTRING(entry, 1, CHARINDEX('(', entry) - 1) AS entry_part
        FROM SplitEntries t
        WHERE entry <> ''
    ) AS temp
),
ExpandedNumbers AS (
    SELECT 
        er.cc_EXPRESSION,
        n.n AS extracted_number
    FROM ExtractRanges er
    JOIN Numbers n ON n.n BETWEEN er.start_num AND er.end_num
)
SELECT 
    cc_EXPRESSION,
    STRING_AGG(extracted_number, ', ') WITHIN GROUP (ORDER BY extracted_number) AS extracted_numbers
FROM ExpandedNumbers
GROUP BY cc_EXPRESSION;

执行后得到的结果：

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