Oracle determines whether it is Numeric (amount)

There is no such method in oracle. You need to write a method to determine whether the data type is Numeric. However, you can also use other methods:

There is no such method in oracle. You need to write a method to determine whether the data type is Numeric. However, you can also use other methods:

On SQL server, it is easier to verify whether the data is Numeric. You can use the following methods:

ISNUMERIC

When it is a number type, the return value of the method is 1; otherwise, the return value is 0.

Example:

Select * from tablename where isnumeric (data) = 1

There is no such method in Oracle. You need to write a method to determine whether the data type is Numeric. However, you can also use other methods:

Method 1: use the regular expression:

Use regexp_like

Example:

Select * from tablename where

Regexp_like (trim (data), '^ ([\-]? [0-9] + \. [0-9] +) $ | ^ ([\-]? [0-9]) + $ | ^ ([\-]? [0-9] {1} \. [0-9] + E [\-]? [0-9]) + $ ')

This regular expression is divided into three parts:

1. ^ ([\-]? [0-9] + \. [0-9] +) $

Float type, including decimal point

2. ^ ([\-]? [0-9]) + $

Integer

3. ^ ([\-]? [0-9] {1} \. [0-9] + E [\-]? [0-9]) + $

Number represented by scientific notation

The preceding three parts contain negative numbers.

Note: regexp_like is applicable to oracle 10 GB and later versions. It is not applicable to earlier versions.

Method 2: Use the regular expression method:

Use the translate method

Example:

Select data, decode (trim (translate (trim (cdata), '2017. ', ''),'', 'is number', 'not number ')

From tablename

However, this is not perfect yet. Negative numbers are not considered. The scientific notation indicates that the data itself is empty, or the verification of "." is not considered.

So pay attention to or modify it when using method 2 (it is a little complicated to write well, so it is better to write a method ).