Determines whether a field is a number in Oracle

In our usual development we may encounter the problem of judging whether a column is all made up of numbers, and we all know that Oracle does not provide us with such a ready-made function, so in my experience I have summed up two proven methods (column name: Columns, table name: Table):

1. Use the Trim+translate function:

Copy CodeThe code is as follows: SELECT * FROM table where trim (Translate (column, ' 0123456789 ', ")) is NULL;

Note here: The third parameter of the Translate function is a space, not a ', because the third parameter of translate if empty, then always return ', this will not achieve the purpose of filtering pure numbers. In this way, all the numbers are converted into spaces, if all are composed of numbers, then once trim is naturally empty, the above goal is achieved. Of course, if you want to exclude empty items, you can write this:

Copy CodeThe code is as follows: SELECT * FROM table where trim (Translate (NVL (column, ' X '), ' 0123456789 ', ') ") is null;--x represents any character other than ' 0-9 '.

2. Use the Regexp_like function:

Copy CodeThe code is as follows: SELECT * FROM table where regexp_like (column, ' ^[0-9]+[0-9]$ ');

Note here: The Regexp_like function is not available in all Oracle versions. Regexp_like is the four functions that Oracle supports regular expressions: one in Regexp_like,regexp_replace,regexp_instr,regexp_substr, please follow the documentation for more details about this.

Determines whether a field is a number in Oracle