ORA-01722: invalid number [message #272876] |
Mon, 08 October 2007 01:55 |
rzkhan
Messages: 370 Registered: March 2005
|
Senior Member |
|
|
to_number function not working...Is there any alternative..
because I need this field in ascending order.
Please help
drop table abc;
create table abc
(
MID varchar2(10)
)
/
insert into abc values ('154');
insert into abc values ('2');
insert into abc values ('3');
insert into abc values ('124');
insert into abc values ('19');
insert into abc values ('310');
insert into abc values ('19');
insert into abc values ('ES502');
insert into abc values ('CS578');
SQL> select * from abc order by mid;
MID
----------
124
154
19
19
2
3
310
CS578
ES502
9 rows selected.
SQL> ed
Wrote file afiedt.buf
1* select * from abc order by to_number(mid)
SQL> /
select * from abc order by to_number(mid)
*
ERROR at line 1:
ORA-01722: invalid number
|
|
|
|
|
|
|
Re: ORA-01722: invalid number [message #273283 is a reply to message #272876] |
Tue, 09 October 2007 18:32 |
|
Kevin Meade
Messages: 2103 Registered: December 1999 Location: Connecticut USA
|
Senior Member |
|
|
1) left append zero to the field to some maximum size, then sort on the resulting character string
2) write your own f_to_number function to ignore errors in conversion and then retrieve/sort using the function
As Michel said, do some searching. I suggest you search
1) orafaq
2) asktomhome
3) metalink
4) google
Good luck, Kevin
|
|
|