在oracle中，select語句查詢欄位中非純數字值

標籤：函數   ref   select   class   regex   article   into   question   sel   

最近，將原來的數字元欄位轉換為數字時，總報錯誤：無效數字。

如何找出其中哪些是非數字字元的記錄？比較麻煩的事。下面是用Oracle DB內建的函數translate可以找出來的

1.建立測試表

Create Table TestChar(
    ITEM_NUMBER VARCHAR2(20)
);

 

2.手工插入測試記錄

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.妙用Oracle 內建函數Translate找出非數字字元的記錄

select trim(translate(RTRIM(LTRIM(ITEM_NUMBER)), ‘#0123456789‘, ‘#‘))
from TestChar
Where trim(translate(RTRIM(LTRIM(ITEM_NUMBER)), ‘#0123456789‘, ‘#‘)) is not null;

 

出處：http://blog.csdn.net/chenxianping/article/details/6338045

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

再看看另外一個寫的，也比較實用。

--1.正則判斷,適用於10g以上版本--非正整數 select 欄位 from 表 where regexp_replace(欄位,‘\d‘,‘‘) is not null;--非數實值型別select 欄位 from 表 where regexp_replace(欄位,‘^[-\+]?\d+(\.\d+)?$‘,‘‘) is not null;--2.自訂函數,判斷非實值型別create or replace function isnumber(col varchar2) return <a href="https://www.baidu.com/s?wd=integer&tn=44039180_cpr&fenlei=mv6quAkxTZn0IZRqIHckPjm4nH00T1YdmWndnynknWfzrjDYuHF90ZwV5Hcvrjm3rH6sPfKWUMw85HfYnjn4nH6sgvPsT6KdThsqpZwYTjCEQLGCpyw9Uz4Bmy-bIi4WUvYETgN-TLwGUv3EPjDvPjDYPHDs" target="_blank" class="baidu-highlight">integer</a> is  i number;begin  i := to_number(col);  return 1;exception  when others then    return 0;end;select 欄位 from 表 where isnumber(欄位)=0;

 

出處：https://zhidao.baidu.com/question/416414510.html

在oracle中，select語句查詢欄位中非純數字值