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使用的范围,必须是数字】
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26442936/viewspace-751142/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/26442936/viewspace-751142/