如何在SQL Server中从字符串提取5位及以上的数字代码？

原始表结构及数据：

ID   **ExpLine**
1    0404079=0.00          
2    1444716<=0.00         
3    1.0<00226311 <= 0.00  
4    0001208 <= 0.00       
5    0.00<0243026<=2.00    
6    0036983 <= 0.00       
7    0036974=0.00          

需求：从ExpLine字段中提取长度为5位及以上的纯数字代码，生成单独列，预期效果如下：

ID   **ExpLine**                  提取结果
1    0404079=0.00                -->  0404079
2    1444716<=0.00               -->  1444716
3    1.0<00226311 <= 0.00        -->  00226311
4    0001208 <= 0.00             -->  0001208
5    0.00<0243026<=2.00          -->  0243026
6    0036983 <= 0.00             -->  0036983
7    0036974=0.00                -->  0036974

解决方案 #

核心逻辑是用正则匹配字符串中连续5位及以上的纯数字序列，自动排除带小数点的数值（如1.0、0.00这类不符合长度或格式的内容）。以下是主流数据库的实现方式：

1. MySQL（8.0+） #

使用REGEXP_SUBSTR直接匹配目标序列：

SELECT
    ID,
    ExpLine,
    REGEXP_SUBSTR(ExpLine, '[0-9]{5,}') AS ExtractedCode
FROM your_table_name;

• 正则[0-9]{5,}表示匹配连续5个及以上的数字，完全符合需求。

2. SQL Server（2017+） #

用REGEXP_REPLACE捕获目标数字并替换整个字符串：

SELECT
    ID,
    ExpLine,
    REGEXP_REPLACE(ExpLine, '.*?([0-9]{5,}).*', '$1') AS ExtractedCode
FROM your_table_name;

• 正则.*?([0-9]{5,}).*会跳过任意前置内容，捕获第一个符合长度要求的数字序列，最终返回该序列。

若版本低于2017，可使用PATINDEX结合字符串截取：

SELECT
    ID,
    ExpLine,
    SUBSTRING(
        ExpLine,
        PATINDEX('%[0-9][0-9][0-9][0-9][0-9]%', ExpLine),
        CHARINDEX(
            ' ', ExpLine + ' ', PATINDEX('%[0-9][0-9][0-9][0-9][0-9]%', ExpLine)
        ) - PATINDEX('%[0-9][0-9][0-9][0-9][0-9]%', ExpLine)
    ) AS ExtractedCode
FROM your_table_name;

3. PostgreSQL #

使用REGEXP_MATCHES获取匹配结果数组的第一个元素：

SELECT
    ID,
    ExpLine,
    (REGEXP_MATCHES(ExpLine, '[0-9]{5,}'))[1] AS ExtractedCode
FROM your_table_name;

4. Oracle（11gR2+） #

使用REGEXP_SUBSTR实现提取：

SELECT
    ID,
    ExpLine,
    REGEXP_SUBSTR(ExpLine, '[0-9]{5,}') AS ExtractedCode
FROM your_table_name;

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