How does Oracle determine whether data is of numeric type?

Method: 1. Use "trim(translate(column,'0123456789',' ')) is NULL;" to determine whether the data is a number; 2. Use "regexp_like(column,'^[0-9 ] [0-9]$');" Determine whether the data is a number.

The operating environment of this tutorial: Windows 10 system, Oracle 11g version, Dell G3 computer.

How oracle determines whether the data is a numeric type

select*from table name wherere In our usual development, we may encounter such a problem, which is to determine whether a certain column is entirely composed of numbers. We all know that Oracle does not provide us with such a ready-made function, so based on my experience I have summarized two effective methods (column name: column, table name: table):

1. Use the trim translate function:

The code is as follows:

select * from table where trim(translate(column,'0123456789',' ')) is NULL;

What should be noted here is that 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 cannot achieve the purpose of filtering pure numbers.

In this way, all numbers are converted into spaces. If they are all composed of numbers, then once trimmed, they will naturally be empty, achieving the above goal. Of course, if you want to exclude empty items, you can write like this:

The code is as follows:

select * from table where trim(translate(nvl(column,'x'),'0123456789',' ')) is NULL;--x 表示任何'0-9'以外的字符。

2. Use the regexp_like function:

The code is as follows:

select * from table where regexp_like(column,'^[0-9]+[0-9]$');

It should be noted here that the regexp_like function is not available in all Oracle versions.

regexp_like is one of the four functions that Oracle supports regular expressions: regexp_like, regexp_replace, regexp_instr, regexp_substr. For more detailed information in this regard, please pay attention to the relevant documents.

Recommended tutorial: "Oracle Video Tutorial"

The above is the detailed content of How does Oracle determine whether data is of numeric type?. For more information, please follow other related articles on the PHP Chinese website!