PostgreSQL中WHERE子句将VARCHAR转为INT/NUMBER的查询问题

针对你遇到的情况——ACTIVITY_CODE是VARCHAR类型，包含数字字符串和非数字值，需要筛选出10012到10015的记录——可以通过以下几种方式实现：

方法1：正则过滤+数值转换（兼容所有PostgreSQL版本） #

首先用正则表达式筛选出纯数字的ACTIVITY_CODE，再转换为整数进行范围判断：

SELECT *
FROM ACTIVITIES_T
WHERE ACTIVITY_CODE ~ '^[0-9]+$'  -- 确保是纯数字字符串
  AND ACTIVITY_CODE::INT BETWEEN 10012 AND 10015;

或者用>=和<=的等价写法：

SELECT *
FROM ACTIVITIES_T
WHERE ACTIVITY_CODE ~ '^[0-9]+$'
  AND ACTIVITY_CODE::INT >= 10012
  AND ACTIVITY_CODE::INT <= 10015;

方法2：使用TRY_CAST（PostgreSQL 12+版本推荐） #

PostgreSQL 12及以上支持TRY_CAST函数，它会在转换失败时返回NULL，无需额外的正则判断，写法更简洁：

SELECT *
FROM ACTIVITIES_T
WHERE TRY_CAST(ACTIVITY_CODE AS INT) BETWEEN 10012 AND 10015;

为什么不能直接用字符串的BETWEEN？ #

直接对VARCHAR类型用ACTIVITY_CODE BETWEEN '10012' AND '10015'会有问题：字符串比较是按字符顺序进行的，比如'999'会被认为比'1000'大（因为第一个字符'9' > '1'），而且非数字值（比如'SPECIAL'）也会参与比较，导致结果不符合预期。必须先转换为数值类型，才能保证范围判断的准确性。

内容的提问来源于stack exchange，提问作者gene b.