SSMS中SQL将带YRS/MOS的Varchar字段转换为总月数的方案咨询

原语句错误原因 #

你之前的写法存在两个核心问题：

• PATINDEX('%[0,9]%',TOJ)里的匹配规则写错，[0,9]匹配的是0、逗号、9三个字符，不是0-9的数字区间，正确的数字匹配规则为%[0-9]%
• 即使修正匹配规则，原写法只能提取从第一个数字到字符串末尾的完整内容，无法拆分年、月两个独立数值，无法完成后续的求和计算。

正确实现代码 #

以下代码适配SQL Server语法（你用到的PATINDEX为SQL Server内置函数），兼容带空格/不带空格的字段格式：

SELECT 
    -- 计算TOJ字段总月数
    CAST(SUBSTRING(REPLACE(TOJ,' ',''), 1, PATINDEX('%YRS%', REPLACE(TOJ,' ','')) - 1) AS INT) * 12
    + CAST(SUBSTRING(REPLACE(TOJ,' ',''), PATINDEX('%YRS%', REPLACE(TOJ,' ','')) + 3, PATINDEX('%MOS%', REPLACE(TOJ,' ','')) - PATINDEX('%YRS%', REPLACE(TOJ,' ','')) - 3) AS INT) AS TOJ_total_months,
    -- 计算TAR字段总月数
    CAST(SUBSTRING(REPLACE(TAR,' ',''), 1, PATINDEX('%YRS%', REPLACE(TAR,' ','')) - 1) AS INT) * 12
    + CAST(SUBSTRING(REPLACE(TAR,' ',''), PATINDEX('%YRS%', REPLACE(TAR,' ','')) + 3, PATINDEX('%MOS%', REPLACE(TAR,' ','')) - PATINDEX('%YRS%', REPLACE(TAR,' ','')) - 3) AS INT) AS TAR_total_months
FROM ld2

逻辑说明 #

• 先用REPLACE(字段名,' ','')去除字段内所有空格，统一8YRS0MOS、8 YRS 0 MOS两类格式
• 用PATINDEX('%YRS%', 处理后字符串)定位YRS的起始位置，从字符串开头截取到该位置前一位，得到年数字，转成INT类型后乘以12
• 再用PATINDEX('%MOS%', 处理后字符串)定位MOS的起始位置，从YRS结束位置（YRS占3个字符）截取到MOS起始位置前一位，得到月数字，转成INT类型
• 年换算的月数加月数字，得到最终总月数

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