to_number question 2004-07-14 - By Wolfgang Breitling
It will also fail in the select:
19:00:40 ora92.scott > select * from lee;
C1
-- ---- ---- -----
459100154152407
1672324589227
75534921326846
895924377396152
619950297203501
890345901043545
519381709242400
404986490556978
741360347483576
706947665719106
702244196248142
364997585414739
375588978751301
500404215742764
220236779017578
401577792893321
718130569761368
907064124732247
409003793863253
557235 557235
300757076,300757
21 rows selected.
19:00:51 ora92.scott > col n1 for 999,999,999,999,999,999
19:01:35 ora92.scott > select to_number(c1) n1 from lee;
N1
-- ---- ---- ---- ------
459,100,154,152,407
1,672,324,589,227
75,534,921,326,846
895,924,377,396,152
619,950,297,203,501
890,345,901,043,545
519,381,709,242,400
404,986,490,556,978
741,360,347,483,576
706,947,665,719,106
702,244,196,248,142
364,997,585,414,739
375,588,978,751,301
500,404,215,742,764
220,236,779,017,578
ERROR:
ORA-01722 (See ORA-01722.ora-code.com): invalid number
At 06:38 PM 7/14/2004, you wrote:
> > -- --Original Message-- --
> > Almost certainly somewhere in your column you have at least
> > one value that fails to convert to a number.
>
>I 'm inclined to agree because the following works:
>to_number(translate(nbr_cc_no_high, '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcd
>efghijklmnopqrstuvwxyz ', '0123456789 '))
>
>Some creator of data around here needs to be slapped.
>
>You still have to wonder why it isn 't a problem in the SELECT part; but IS a
>problem in WHERE part.
>
>-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ------
>Please see the official ORACLE-L FAQ: http://www.orafaq.com
>-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ------
>To unsubscribe send email to: oracle-l-request@(protected)
>put 'unsubscribe ' in the subject line.
>--
>Archives are at http://www.freelists.org/archives/oracle-l/
>FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
>-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- --
Regards
Wolfgang Breitling
Centrex Consulting Corporation
www.centrexcc.com
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ------
To unsubscribe send email to: oracle-l-request@(protected)
put 'unsubscribe ' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- --
|
|
