可移植地确定字符串是否可转换为整数

Question

我想将一个VARCHAR转换为SQL中的INTEGER，如果该字段中的某个值无法正确转换，则提供默认值。类似于：

sql> SELECT str FROM tbl;  -- CREATE TABLE tbl (str VARCHAR(12), ...)
    str
========
  12345
     -1
    foo

sql> SELECT CAST((CASE WHEN ... THEN str ELSE '-9999' END) AS INTEGER) AS "int" FROM tbl;
    int
========
  12345
     -1
  -9999

我应该在省略号中放什么以获得所需的结果？

这个问题已经在SO上为许多特定的数据库进行了问答，但我想知道是否有一种更或多或少可移植的方法来实现这一点？

- pilcrow

---

没有通用的方法来做这件事。 - Vadim K.

你最好将整数数据存储在整数字段中。不要混合字符串和整数数据。 - HLGEM

@HLGEM，你当然是对的，但那不是我所处的环境。 - pilcrow

@Vadim K.，为什么不把你的评论提升为答案呢？ - pilcrow

也许是时候重构你当前的环境了。 - HLGEM

Answer 1

这是一种“名义上可移植”的方法，使用ANSI SQL-99的简单伪POSIX regexen和“SIMILAR TO”操作符：

CAST ((CASE
       WHEN string_column -- in perl:  $string_column =~ /^\s*[+-]?\d+\s*$/
            SIMILAR TO
            '[[:space:]]*([+-]|)[[:digit:]]+[[:space:]]*'
       THEN
         string_column
       ELSE
         '-9999'         -- default value for un-CASTable strings
       END)
      AS INTEGER

我说“名义上可移植”是因为SIMILAR TO并不被广泛支持。（PostgreSQL已经有了一段时间，Firebird née Interbase承诺在即将发布的2.5版本中实现，但仅此而已。）

- pilcrow

Answer 2

鉴于 INTEGER 类型（32 位？64 位？）所能接受的值的范围因实现而异，没有一种非厂商特定的方法来执行此操作。

- Matthew Flynn

---

+1，你提出了一个很好的考虑。然而，假设INT大小不是问题--比如我受限于VARCHAR（3），因此不会溢出任何实际的INTEGER实现。 - pilcrow