obstacle10.jpg I have a varchar in a table on an Oracle 8i database, and I wanted to select only those tuples that contain numbers.

Had I used PostgreSQL I could just have used a regex, which is nice and Perl like. But this is Oracle and so the “Enterprise solution” to this problem looks loke this:

1
2
3
SELECT * FROM dual
WHERE length(trim(TRANSLATE('NaN', '0123456789', '          ')))
IS NULL;

And whenever the above statement is different from null, it is not a number…

Related posts:

  1. Ant sucks, and doesn’t respect symlinks…