Is Number/Numeric

by Yuval Ararat on 22/05/2008

A cute where statement to figure out if a field is numeric.
works on Oracle 10g and might work on others with small adjustments.
It will return only numeric fields
LENGTH(<Your field>) - LENGTH(TRANSLATE(<Your field>, CHR(1)|| TRANSLATE(<Your field>, CHR(1)||'-.1234567890', CHR(1)), CHR(1) ) ) = 0 AND INSTR(<Your field>,'.',1,2) = 0
Enjoy