16 October 2009

Oracle numeric test

A colleague and I found ourselves needing to write a one-off bit of SQL that used a character column in a join condition. The character column is performing double duty, sometimes acting as a (numeric) foreign key, and sometimes holding other data.

I was perplexed by the lack of some kind of "is numeric" test in Oracle's dialect of SQL. I scrounged around forums and found something that I thought would work, but my colleague finally put me straight and we used this condition:

REGEXP_LIKE(char_column, '^[[:digit:]]+$')

No comments: