In Oracle, the SELECT statement queries the field for non-pure numeric values

Recently, when converting the original numeric character field to a number, the general report error: Invalid number.

How do I find out which non-numeric characters are recorded? It's a bit of a hassle. The following is an Oracle DB-translate function that can be found

1. Create a test table

Create Table Testchar (
Item_Number VARCHAR2 (20)
);

2. Insert the Test record manually

Insert into Testchar (item_number) VALUES (' 312 ');
Insert into Testchar (item_number) VALUES (' 312 ');
Insert into Testchar (item_number) VALUES (' 4412 ');
Insert into Testchar (item_number) VALUES (' 152 ');
Insert into Testchar (item_number) VALUES (' 162 ');
Insert into Testchar (item_number) VALUES (' 172 ');
Insert into Testchar (item_number) VALUES (' 142 ');
Insert into Testchar (item_number) VALUES (' 142 ');
Insert into Testchar (item_number) VALUES (' 112 ');
Insert into Testchar (item_number) VALUES (' 1d2 ');
Insert into Testchar (item_number) VALUES (' 152 ');
Insert into Testchar (item_number) VALUES (' 125 ');
Insert into Testchar (item_number) VALUES (' 162 ');
Insert into Testchar (item_number) VALUES (' 712 ');
Insert into Testchar (item_number) VALUES (' A712 ');
Commit

3. Magical Oracle Built-in function translate find records of non-numeric characters

Select Trim (Translate (RTRIM (LTRIM (Item_Number)), ' #0123456789 ', ' # ')
From Testchar
Where Trim (Translate (RTRIM (LTRIM (Item_Number)), ' #0123456789 ', ' # ') are NOT null;

Source: http://blog.csdn.net/chenxianping/article/details/6338045

=======================================================

Look at another one to write, also more practical.

--1. Regular judgment, applicable to 10g or higher--non-positive integersSelectField fromTablewhereRegexp_replace (field,'\d',"') is  not NULL;--non-numeric typeSelectField fromTablewhereRegexp_replace (field,'^[-\+]?\d+ (\.\d+)? $',"') is  not NULL;--2. Customize functions to determine non-value typesCreate or Replace functionIsnumber (colvarchar2)return <a href="https://Www.baidu.com/S?wd=integer&Tn=44039180_cpr&Fenlei=Mv6quakxtzn0izrqihckpjm4nh00t1ydmwndnynknwfzrjdyuhf90zwv5hcvrjm3rh6spfkwumw85hfynjn4nh6sgvpst6kdthsqpzwytjceqlgcpyw9uz4bmy-Bii4wuvyetgn-Tlwguv3epjdvpjdyphds "target="_blank" class="Baidu-Highlight>integer</A>  isI Number;beginI:=To_number (COL); return 1; exception whenOthers Then    return 0;End;SelectField fromTablewhereIsnumber (field)=0;

Source: https://zhidao.baidu.com/question/416414510.html

In Oracle, the SELECT statement queries the field for non-pure numeric values