SQL中从nvarchar列提取数值：截取后续字符及异常处理咨询

核心问题分析 #

你当前的语句已经能提取第一个数字开始的子串，但无法去除后续非数字内容；同时需要处理无数字、包含日期的场景返回null。下面给出分步解决方案：

基础解决方案代码 #

SELECT 
    CASE
        -- 无数字直接返回null
        WHEN PATINDEX('%[0-9]%', [Description]) = 0 THEN NULL
        ELSE
            -- 提取合法数值部分并转成decimal，非法格式自动返回null
            TRY_CAST(
                LEFT(
                    -- 从第一个数字开始截取子串
                    SUBSTRING([Description], PATINDEX('%[0-9]%', [Description]), LEN([Description])),
                    -- 定位第一个非数字/非小数点的位置，截取到该位置前
                    CASE 
                        WHEN PATINDEX('%[^0-9.]%', SUBSTRING([Description], PATINDEX('%[0-9]%', [Description]), LEN([Description]))) > 0 
                        THEN PATINDEX('%[^0-9.]%', SUBSTRING([Description], PATINDEX('%[0-9]%', [Description]), LEN([Description]))) - 1
                        ELSE LEN(SUBSTRING([Description], PATINDEX('%[0-9]%', [Description]), LEN([Description])))
                    END
                ) AS DECIMAL(10,2)
            )
    END AS Test
FROM YourTable;

代码说明 #

1. 无数字场景处理：通过PATINDEX('%[0-9]%', [Description]) = 0直接返回null。
2. 提取数字起始子串：保留你原逻辑中从第一个数字开始截取的部分。
3. 去除右侧非数字内容：用PATINDEX('%[^0-9.]%', ...)定位第一个非数字、非小数点的字符，截取到该位置前的内容。
4. 合法性校验：TRY_CAST会自动过滤非法格式（比如多个小数点、日期截取的部分数字），返回null，正好处理日期场景的误提取问题。

日期场景精准优化 #

如果需要排除仅含日期的字符串（避免提取日期中的数字），可以增加日期格式判断：

SELECT 
    CASE
        -- 匹配MM/DD/YYYY或YYYY-MM-DD格式的纯日期字符串，返回null
        WHEN [Description] LIKE '%[0-9][0-9]/[0-9][0-9]/[0-9][0-9][0-9][0-9]%' 
             AND PATINDEX('%[^0-9/]%', [Description]) = 0
        THEN NULL
        WHEN [Description] LIKE '%[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]%'
             AND PATINDEX('%[^0-9-]%', [Description]) = 0
        THEN NULL
        -- 原提取逻辑
        WHEN PATINDEX('%[0-9]%', [Description]) = 0 THEN NULL
        ELSE
            TRY_CAST(
                LEFT(
                    SUBSTRING([Description], PATINDEX('%[0-9]%', [Description]), LEN([Description])),
                    CASE 
                        WHEN PATINDEX('%[^0-9.]%', SUBSTRING([Description], PATINDEX('%[0-9]%', [Description]), LEN([Description]))) > 0 
                        THEN PATINDEX('%[^0-9.]%', SUBSTRING([Description], PATINDEX('%[0-9]%', [Description]), LEN([Description]))) - 1
                        ELSE LEN(SUBSTRING([Description], PATINDEX('%[0-9]%', [Description]), LEN([Description])))
                    END
                ) AS DECIMAL(10,2)
            )
    END AS Test
FROM YourTable;

保留原小数格式的调整 #

如果需要和你期望的结果完全一致（比如保留135.4而非135.40），可以改用字符串处理去除末尾多余的0和小数点：

SELECT 
    CASE
        WHEN PATINDEX('%[0-9]%', [Description]) = 0 THEN NULL
        ELSE
            -- 去除末尾多余的0和小数点
            REPLACE(RTRIM(REPLACE(
                LEFT(
                    SUBSTRING([Description], PATINDEX('%[0-9]%', [Description]), LEN([Description])),
                    CASE 
                        WHEN PATINDEX('%[^0-9.]%', SUBSTRING([Description], PATINDEX('%[0-9]%', [Description]), LEN([Description]))) > 0 
                        THEN PATINDEX('%[^0-9.]%', SUBSTRING([Description], PATINDEX('%[0-9]%', [Description]), LEN([Description]))) - 1
                        ELSE LEN(SUBSTRING([Description], PATINDEX('%[0-9]%', [Description]), LEN([Description])))
                    END
                ), '0', ' '
            )), ' ', '0')
    END AS Test
FROM YourTable;

内容的提问来源于stack exchange，提问作者E.Bibler