Portably determining whether string is castable to integer

Question

I'd like to cast a VARCHAR to a SQL INTEGER, supplying a default value if some value in the field would not convert properly. Something like:

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 produce the desired results?

This question has been asked and answered on SO for many specific DBs, but I'm wondering if there's a more-or-less portable way to achieve this?

Answer 1

Given that the range of values acceptbale for INTEGER (32 bits? 64 bits?) varies from implementation to implementation, there is no non-vendor specific way to do this.

Answer 2

Here's a nominally portable approach, using ANSI SQL-99's simple, pseudo-POSIX regexen with the SIMILAR TO operator:

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

I say "nominally portable" because SIMILAR TO is not widely supported. (PostgreSQL has had it for a while, Firebird née Interbase promises it in the upcoming 2.5 release, but that's about it.)