SQL查询优化：如何实现仅将纯数字字符串转为整数，非纯数字时返回NULL

我完全懂你的困扰！原来的正则替换思路是把所有非数字字符都去掉，不管原字符串是不是纯数字，结果像日期这种带数字的混合字符串，会被处理成一串超长数字，最后因为超出整数范围报错——这显然不是你想要的效果。你真正需要的是：只有当整个字符串是合法的整数格式时，才把它转成整数；如果是日期、带字母的字符串或者其他非纯数字内容，直接返回NULL。

其实这个需求实现起来很简单，不同的SQL数据库都有对应的解决方案，优先用数据库自带的“尝试转换”类函数，实在没有的话用正则判断配合条件分支也能搞定，下面给你分情况说明：

一、优先用数据库原生的「尝试转换」函数（推荐） #

很多现代数据库都提供了专门的函数，会尝试把字符串转成指定类型，失败就返回NULL，完美契合你的需求：

PostgreSQL #

直接用TRY_CAST或者TRY_TO_INT函数就行，写法超简洁：

SELECT TRY_CAST(response AS INTEGER) FROM my_table;

不管是日期字符串、带字母的内容还是超长数字，只要无法转成合法整数，都会返回NULL；只有纯数字的字符串会被正确转成整数。

MySQL 8.0.17+ / SQL Server #

这俩数据库也支持TRY_CAST函数，用法和PostgreSQL几乎一样：

-- MySQL 8.0.17+ 或 SQL Server
SELECT TRY_CAST(response AS INT) FROM my_table;

同样，转换失败（包括非纯数字、数字超出整数范围等情况）时自动返回NULL。

二、旧版本数据库的兼容方案（无TRY_CAST时） #

如果你的数据库版本比较老，不支持TRY_CAST，可以用CASE语句配合正则表达式，先判断字符串是否是纯数字格式，再决定是否转换：

MySQL 8.0.17之前的版本 #

用正则匹配整个字符串是否符合整数格式（支持正负号），再转换：

SELECT
  CASE
    -- 先排除空字符串和NULL，再匹配纯数字（支持负数）
    WHEN response IS NOT NULL AND response REGEXP '^-?[0-9]+$' THEN CAST(response AS SIGNED)
    ELSE NULL
  END AS numeric_response
FROM my_table;

如果只需要正整数，把正则改成^[0-9]+$就行。

其他不支持TRY_CAST的数据库 #

核心思路都是一样的：先通过正则^-?[0-9]+$判断字符串是否是合法整数格式，再用CASE分支返回转换结果或NULL。

为什么原来的方法会出问题？ #

再回头说下你原来的查询：CAST((NULLIF(REGEXP_REPLACE(response, '[^0-9]+', '', 'g'), ''), '0') AS INTEGER)
这个逻辑是提取所有数字字符拼接成新字符串，而不是判断原字符串是不是纯数字。比如日期2024-05-20 12:34会被处理成202405201234，这个数字远超出了INTEGER的存储范围，自然会触发转换错误。而我们上面的方案是从“判断原字符串是否合法”入手，从根源上避免了这种问题。

内容来源于stack exchange