電話區號去前面的0、varchar2轉換成number並比較（to_number注意事項）

1、oracle去掉電話區號前面的0

SQL> desc test1
Name  Type         Nullable Default Comments
----- ------------ -------- ------- --------
PHONE VARCHAR2(20) Y                        
CITY  VARCHAR2(20) Y                        
 
SQL> select * from test1;
 
PHONE                CITY
-------------------- --------------------
010                  北京
0451                 哈爾濱
 
SQL> select * from test1 where to_char(phone,'fm9999999')>9;
 
PHONE                CITY
-------------------- --------------------
010                  北京
0451                 哈爾濱
 
SQL> select to_char(phone,'fm9999999'),t.* from test1 t where to_char(phone,'fm9999999')>9;
 
TO_CHAR(PHONE,'FM9999999') PHONE                CITY
-------------------------- -------------------- --------------------
10                         010                  北京
451                        0451                 哈爾濱

2、varchar2轉換成number並比較大小

SQL> select * from test;
 
        ID MONEY
---------- --------------------------------------------------------------------------------
         1 2456
         2 3456
         3 39456
         4 39416
 
SQL> desc test
Name  Type          Nullable Default Comments
----- ------------- -------- ------- --------
ID    NUMBER        Y                        
MONEY VARCHAR2(200) Y                        
 
SQL> select * from test where to_number(money,'999999')>10000;
 
        ID MONEY
---------- --------------------------------------------------------------------------------
         3 39456
         4 39416
 
SQL> insert into test values (5,'$11111') ;
 
1 row inserted
 
SQL> commit;
 
Commit complete
 
SQL>  select * from test where to_number(money,'999999')>10000;
 
select * from test where to_number(money,'999999')>10000
 
ORA-01722: 無效數字（$11111'導致錯誤）
【to_number使用的範圍，必須是數字】