Portable determination of whether a string is valid for an integer

Question

I would like to add VARCHARto SQL INTEGERspecifying a default value if some value in the field will not be converted correctly. Sort of:

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

What could I put in the ellipsis above to get the desired results?

This question has been asked and answered by SO for many specific databases, but I wonder if there is a more or less portable way to achieve this?

Answer 1

Considering that the range of acceptbale values ​​for INTEGER (32 bits? 64 bits?) Varies from implementation to implementation, there is no special method for this that is not related to the provider.

Answer 2

, , -POSIX- ANSI SQL-99 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 promises 2.5, .)