sql - 在 WHERE 子句中将 VARCHAR 转换为 INT/NUMBER

Question

我有一张 table ACTIVITIES_T在 PostgreSQL 数据库中。 ACTIVITY_CODE是 VARCHAR专栏:

ID (INT)  |  ACTIVITY_CODE (VARCHAR) | ACTIVITY_TEXT (VARCHAR)
-------------------------------------------------------------
..            ..                      ..
7             10012                   ..
8             10013                   ..
9             10014                   ..
10            10015                   ..
11            SPECIAL                 ..

我有一个连续的伪数 block ，10012..10015 ，我需要用 BETWEEN a AND b 选择运算符(operator)，或 >= n1 and <= n2 .

但不是 ACTIVITY_CODE 中的所有条目是数值。
我试过这个:

select * from activities_t  where activity_code::int >= 10012 and 
                                  activity_code::int <= 10015;

但得到了错误

ERROR:  invalid input syntax for integer: SPECIAL

Answer 1

SELECT *
FROM   activities_t
WHERE  CASE WHEN activity_code ~ '^\d+$'  -- only digits
            THEN activity_code::int BETWEEN 10012 AND 10015 END;

CASE包装器避免了异常。仅考虑仅由数字组成的字符串。 (所以也没有空白填充!)
不需要 ELSE 因为 CASE 默认为 NULL 并且只有 TRUE 符合 WHERE条款。

当然，这不能在 activity_code 上使用任何普通索引。

如果您需要更好的性能，请清理您的表设计，或者，如果这不是一个选项，请创建一个表达式索引:

CREATE INDEX foo_idx ON activities_t((CASE WHEN activity_code ~ '^\d+$'
                                           THEN activity_code::int END));

并匹配查询中的表达式:

SELECT *, CASE WHEN activity_code ~ '^\d+$' THEN activity_code::int END
FROM   activities_t
WHERE  CASE WHEN activity_code ~ '^\d+$'
            THEN activity_code::int END BETWEEN 10012 AND 10015;

这与第一个查询略有不同，如果它不触发索引扫描，代价会稍微高一些。