SQL Server中使用Substring与CharIndex提取指定子串的问题

针对需要从冒号分隔的字符串中提取各段数字前缀，再拼接成xxx-xxx-xxx格式的需求，提供两种可行的SQL实现方案：

方案一：固定分段场景（已知为3段） #

适用于字符串固定由3个冒号分隔段组成的情况，直接拆分每一段并提取数字：

SELECT 
    CONCAT(
        -- 提取第一段数字前缀
        LEFT(SUBSTRING(target_str, 1, CHARINDEX(':', target_str) - 1),
             PATINDEX('%[^0-9]%', SUBSTRING(target_str, 1, CHARINDEX(':', target_str) - 1)) - 1),
        '-',
        -- 提取第二段数字前缀
        LEFT(SUBSTRING(target_str, CHARINDEX(':', target_str) + 1, CHARINDEX(':', target_str, CHARINDEX(':', target_str) + 1) - CHARINDEX(':', target_str) - 1),
             PATINDEX('%[^0-9]%', SUBSTRING(target_str, CHARINDEX(':', target_str) + 1, CHARINDEX(':', target_str, CHARINDEX(':', target_str) + 1) - CHARINDEX(':', target_str) - 1)) - 1),
        '-',
        -- 提取第三段数字前缀
        LEFT(SUBSTRING(target_str, CHARINDEX(':', target_str, CHARINDEX(':', target_str) + 1) + 1, LEN(target_str)),
             PATINDEX('%[^0-9]%', SUBSTRING(target_str, CHARINDEX(':', target_str, CHARINDEX(':', target_str) + 1) + 1, LEN(target_str))) - 1)
    ) AS result_str
FROM your_table;

说明：通过CHARINDEX定位冒号位置拆分字符串，PATINDEX找到第一个非数字字符的位置，LEFT提取前面的数字部分，最终用CONCAT拼接成目标格式。

方案二：通用分段场景（支持任意段数） #

适用于SQL Server 2016及以上版本，通过拆分字符串后统一处理每一段，再拼接结果：

WITH split_segments AS (
    SELECT 
        target_str,
        -- 提取每一段的数字前缀，兼容纯数字段
        LEFT(value, CASE 
                        WHEN PATINDEX('%[^0-9]%', value) = 0 THEN LEN(value)
                        ELSE PATINDEX('%[^0-9]%', value) - 1
                    END) AS num_part,
        -- 生成分段序号，保证拼接顺序与原字符串一致
        ROW_NUMBER() OVER (PARTITION BY target_str ORDER BY CHARINDEX(':' + value + ':', ':' + target_str + ':')) AS segment_order
    FROM your_table
    CROSS APPLY STRING_SPLIT(target_str, ':')
)
SELECT 
    STRING_AGG(num_part, '-') WITHIN GROUP (ORDER BY segment_order) AS result_str
FROM split_segments
GROUP BY target_str;

说明：用STRING_SPLIT拆分字符串，CASE处理纯数字分段的情况，ROW_NUMBER确保分段顺序与原字符串一致，最后用STRING_AGG按顺序拼接成连字符分隔的格式。

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