# oracle cast用法 - SQL錯誤“ORA-01722：無效的數字”

## convert conversion (11)

ORA-01722：無效的號碼

``````INSERT INTO CUSTOMER VALUES (1,'MALADY','Claire','27 Smith St Caulfield','0419 853 694');
INSERT INTO CUSTOMER VALUES (2,'GIBSON','Jake','27 Smith St Caulfield','0415 713 598');
INSERT INTO CUSTOMER VALUES (3,'LUU','Barry','5  Jones St Malvern','0413 591 341');
INSERT INTO CUSTOMER VALUES (4,'JONES','Michael','7  Smith St Caulfield','0419 853 694');
INSERT INTO CUSTOMER VALUES (5,'MALADY','Betty','27 Smith St Knox','0418 418 347');
``````

``````SELECT t.col1, t.col2, ('test' + t.col3) as test_col3
FROM table t;
``````

``````cast(regexp_replace('0419 853 694', '[^0-9]+', '') as number)
``````

``````create table telephone_number (tel_number number);
insert into telephone_number values ('0419 853 694');
``````

ORA-01722：無效的號碼

`select max(acc_num) from ACCOUNTS where acc_num between 1001000 and 1001999;`

`select max(acc_num) from ACCOUNTS where acc_num between '1001000' and '1001999';`

...並且瞧：它返回了預期的結果。

``````from __future__ import print_function
import timeit

prep_base = '''\
x = 'invalid'
y = '5402'
z = '4.754e3'
'''

prep_try_method = '''\
def is_number_try(val):
try:
float(val)
return True
except ValueError:
return False

'''

prep_re_method = '''\
import re
float_match = re.compile(r'[-+]?\d*\.?\d+(?:[eE][-+]?\d+)?\$').match
def is_number_re(val):
return bool(float_match(val))

'''

fn_method = '''\
from fastnumbers import isfloat

'''

print('Try with non-number strings', timeit.timeit('is_number_try(x)',
prep_base + prep_try_method), 'seconds')
print('Try with integer strings', timeit.timeit('is_number_try(y)',
prep_base + prep_try_method), 'seconds')
print('Try with float strings', timeit.timeit('is_number_try(z)',
prep_base + prep_try_method), 'seconds')
print()
print('Regex with non-number strings', timeit.timeit('is_number_re(x)',
prep_base + prep_re_method), 'seconds')
print('Regex with integer strings', timeit.timeit('is_number_re(y)',
prep_base + prep_re_method), 'seconds')
print('Regex with float strings', timeit.timeit('is_number_re(z)',
prep_base + prep_re_method), 'seconds')
print()
print('fastnumbers with non-number strings', timeit.timeit('isfloat(x)',
prep_base + 'from fastnumbers import isfloat'), 'seconds')
print('fastnumbers with integer strings', timeit.timeit('isfloat(y)',
prep_base + 'from fastnumbers import isfloat'), 'seconds')
print('fastnumbers with float strings', timeit.timeit('isfloat(z)',
prep_base + 'from fastnumbers import isfloat'), 'seconds')
print()
``````
``````Try with non-number strings 2.39108395576 seconds
Try with integer strings 0.375686168671 seconds
Try with float strings 0.369210958481 seconds

Regex with non-number strings 0.748660802841 seconds
Regex with integer strings 1.02021503448 seconds
Regex with float strings 1.08564686775 seconds

fastnumbers with non-number strings 0.174362897873 seconds
fastnumbers with integer strings 0.179651021957 seconds
fastnumbers with float strings 0.20222902298 seconds
``````

• `try: except:`數字輸入快，但對於無效輸入非常慢
• 當輸入無效時，正則表達式非常有效
• `fastnumbers`在兩種情況下`fastnumbers`獲勝