SQL Server从varchar类型age字段提取整数并分组的问题求助

问题根源 #

原代码用SUBSTRING固定截取前2个字符，遇到单数字年龄（如'8-year old'）会截取到'8-'，转换为int时触发报错；同时未处理'ABCD'这类非数字格式的字段值，也会导致转换失败。

解决方案 #

通过PATINDEX定位第一个非数字字符的位置，精准提取年龄数字部分，再用TRY_CAST安全转换为数值（转换失败返回NULL），最后通过CASE完成分组：

SELECT
    CASE 
        WHEN TRY_CAST(
            SUBSTRING(
                TRIM(age),
                1,
                CASE PATINDEX('%[^0-9]%', TRIM(age))
                    WHEN 0 THEN LEN(TRIM(age))
                    ELSE PATINDEX('%[^0-9]%', TRIM(age)) - 1
                END
            ) AS INT
        ) >= 65  
        THEN '65 Years Old and older' 
        ELSE 'Under 65 Years Old'
    END AS age_group 
FROM
    your_table_name

代码细节说明 #

• TRIM(age)：先去除字段值前后空格，适配' 2 -year old '这类带多余空格的格式。
• PATINDEX('%[^0-9]%', TRIM(age))：定位第一个非数字字符的位置，比如'62-year old'返回3，'8-year old'返回2，'ABCD'返回1，纯数字则返回0。
• SUBSTRING(...)：根据非数字位置截取前半段的数字字符串，纯数字则截取整个字符串。
• TRY_CAST(...) AS INT：安全转换为整数，非数字内容会返回NULL，彻底避免转换报错。
• CASE判断：转换后的年龄≥65时标记对应分组，其余情况（包括转换失败的NULL）统一标记为Under 65 Years Old。

非数字格式的特殊处理 #

如果需要对'ABCD'这类无效年龄单独标记，可以修改CASE语句：

SELECT
    CASE 
        WHEN TRY_CAST(
            SUBSTRING(
                TRIM(age),
                1,
                CASE PATINDEX('%[^0-9]%', TRIM(age))
                    WHEN 0 THEN LEN(TRIM(age))
                    ELSE PATINDEX('%[^0-9]%', TRIM(age)) - 1
                END
            ) AS INT
        ) >= 65  
        THEN '65 Years Old and older'
        WHEN TRY_CAST(
            SUBSTRING(
                TRIM(age),
                1,
                CASE PATINDEX('%[^0-9]%', TRIM(age))
                    WHEN 0 THEN LEN(TRIM(age))
                    ELSE PATINDEX('%[^0-9]%', TRIM(age)) - 1
                END
            ) AS INT
        ) IS NOT NULL
        THEN 'Under 65 Years Old'
        ELSE 'Invalid Age'
    END AS age_group 
FROM
    your_table_name

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