如何用SQL转换指定列子串计算以筛选符合剩余年限要求的合同

• 子串截取逻辑容错性极低：你使用POSITION取第一个空格位置截取左侧年限，只要字段值存在开头空格、格式不规范等问题，取到的内容就不是有效数字，CAST转换会直接报错或得到错误值；用RIGHT(Term,4)取生效年份同理，只要字段末尾有多余空格、换行符或其他异常后缀，截取到的4位字符就不是年份，计算结果自然不符合预期。
• 计算逻辑精度不符合业务要求：直接按年份差值计算会出现边界误差，比如当前是2024年3月，合同到期日是2033年12月，按你的逻辑计算结果为2033-2024=9，会被判定为剩余年限不足10年，但实际剩余时长还有9年9个月，和业务层面的“不足10年”判定标准可能存在偏差。
• 无脏数据过滤逻辑：你明确提到表的数据质量较差，遇到空值、格式完全不匹配的Term字段时，你的SQL会直接运行报错，或返回大量无效结果。

以下为MySQL环境下的实现代码，其他数据库可对应替换字符串、日期函数即可：

精确到天的计算方案（推荐） #

按照实际日期差值判断剩余年限，避免边界误差：

SELECT * FROM mydb
WHERE 
-- 先过滤格式符合要求的记录，避免脏数据导致计算报错
`Term` REGEXP '^[0-9]+ years from [0-9]{1,2} [A-Za-z]+ [0-9]{4}$'
AND 
-- 计算到期日和当前日期的年份差，判断是否小于10年
TIMESTAMPDIFF(YEAR, CURDATE(), 
  -- 生效日加合同年限得到到期日
  DATE_ADD(
    -- 提取日期字符串转换为日期类型
    STR_TO_DATE(SUBSTRING_INDEX(`Term`, 'from ', -1), '%d %M %Y'),
    INTERVAL CAST(SUBSTRING_INDEX(`Term`, ' ', 1) AS UNSIGNED) YEAR
  )
) < 10

如果Term字段存在大小写不统一的问题（比如Years、FROM），可以调整正则为忽略大小写规则即可。

仅按年份计算的简化方案 #

如果业务不需要精确到天，仅看自然年差值，可以使用简化版本，建议仍然保留格式校验逻辑：

SELECT * FROM mydb
WHERE 
`Term` REGEXP '^[0-9]+ years from [0-9]{1,2} [A-Za-z]+ [0-9]{4}$'
AND 
CAST(SUBSTRING_INDEX(`Term`, ' ', 1) AS UNSIGNED) + CAST(RIGHT(SUBSTRING_INDEX(`Term`, ' ', -1),4) AS UNSIGNED) - YEAR(CURDATE()) < 10

• 如果该表数据量很大、该筛选逻辑使用频率高，建议新增单独的contract_expire_date字段，提前将Term中的到期日计算好存储，再新增索引，性能远高于每次查询时实时做字符串和日期计算。
• 可以先运行以下SQL捞出不符合格式的脏数据单独处理，避免漏掉有效合同：

SELECT Term, SUBSTRING_INDEX(`Term`, ' ', 1) AS 合同年限, SUBSTRING_INDEX(`Term`, 'from ', -1) AS 生效日期 
FROM mydb 
WHERE `Term` NOT REGEXP '^[0-9]+ years from [0-9]{1,2} [A-Za-z]+ [0-9]{4}$'

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