SQL中提取格式不统一字符串末尾连续数字组的实现方案

核心思路是通过正则匹配提取字符串末尾的连续数字，转换为整数后再做判断，可同时解决截取到非数字字符、前导零、字符串比较逻辑错误三个问题。

支持正则函数的数据库（MySQL 8.0+/Oracle/PostgreSQL/SQL Server 2017+） #

使用REGEXP_SUBSTR函数匹配末尾连续数字，写法最简洁：

SELECT 
  INBOX_COL,
  -- 提取末尾连续数字并转成整数，自动去除前导零
  CAST(REGEXP_SUBSTR(INBOX_COL, '[0-9]+$') AS UNSIGNED) AS size_num,
  CASE WHEN CAST(REGEXP_SUBSTR(INBOX_COL, '[0-9]+$') AS UNSIGNED) > 16 THEN 6 ELSE 12 END AS QTY
FROM MY_TABLE

注：不同数据库的整数转换函数可自行替换，比如SQL Server用TRY_CAST(xxx AS INT)避免非法值报错。

低版本SQL Server兼容写法（不支持正则） #

通过字符串倒序找第一个非数字位置再截取，逻辑如下：

SELECT 
  INBOX_COL,
  CAST(REVERSE(LEFT(REVERSE(RTRIM(INBOX_COL)), PATINDEX('%[^0-9]%', REVERSE(RTRIM(INBOX_COL)) + 'X') - 1)) AS INT) AS size_num,
  CASE WHEN CAST(REVERSE(LEFT(REVERSE(RTRIM(INBOX_COL)), PATINDEX('%[^0-9]%', REVERSE(RTRIM(INBOX_COL)) + 'X') - 1)) AS INT) > 16 THEN 6 ELSE 12 END AS QTY
FROM MY_TABLE

效果验证 #

用你提供的样例测试，输出结果完全符合预期：

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