Judging whether a field is a number in Oracle

Time:2020-11-17

In our normal development, we may encounter the problem of judging whether a column is composed of numbers. We all know that Oracle does not provide us with such a ready-made function. According to my experience, I have summed up two effective methods (column name: column, table name: table)

Use the translate + 1

Copy codeThe code is as follows:
select * from table where trim(translate(column,’0123456789′,’ ‘)) is NULL;

What should be noted here is: the third parameter of the translate function is a space, not ”, because if the third parameter of translate is empty, it will always return ”, which will not achieve the purpose of filtering pure numbers. In this way, all the numbers are converted into spaces. If all the numbers are composed of numbers, they will be empty after trim. Of course, if you want to exclude empty items, you can write as follows:

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 regexp_ Like function:

Copy codeThe code is as follows:
select * from table where regexp_like(column,’^[0-9]+[0-9]$’);

Here we should pay attention to:regexp_ The like function is not available in all Oracle versions.regexp_ Like is the four functions that Oracle supports regular expressions: regexp_ like,regexp_ replace,regexp_ instr,regexp_ For more details on this, please pay attention to the relevant documents.

In summary, if regexp supports_ Regular function is recommended. If it is not supported, trim + translate is used.