SQL> select serialno sn,xuetang xt from medical 2 where to_number(xuetang) between 10 and 50; where to_number(xuetang) between 10 and 50 * ERROR 位于第2行: ORA-01722: 无效数字SQL> select serialno sn,xuetang xt from medical 2 where xuetang between '10' and '50';SN XT ---- -------------------- 0001 5.6mmol <<<<<<<错误 0002 10.2 0003 21.5 0004 13.1
2 where to_number(xuetang) between 10 and 50;
where to_number(xuetang) between 10 and 50
*
ERROR 位于第2行:
ORA-01722: 无效数字SQL> select serialno sn,xuetang xt from medical
2 where xuetang between '10' and '50';SN XT
---- --------------------
0001 5.6mmol <<<<<<<错误
0002 10.2
0003 21.5
0004 13.1
用DECODE的函數在理論上可以解決但是實際上非常的難處理可行的一個方法是編一個函數,把XT列的數值都轉換為數字。
另外:如何把数字转化掉?我明天开新贴子,希望有好答案哦。 :)
SQL> select serialno sn,xuetang xt from medical
2 where
3 TRANSLATE(xuetang,'0123456789.ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz',
4 '0123456789.')
5 between 10 and 50;SN XT
---- --------------------
0002 10.22
0003 21.5
0004 13.1
使用这个函数同样可以实现的
SQL> select translate('dfadfas3234234','a0123456789','a') from dual;TRANSLATE('DFADFAS3234234','A0
------------------------------
dfadfasSQL>