Home » SQL & PL/SQL » SQL & PL/SQL » ORA-01722: invalid number (Converting to Number Error)
ORA-01722: invalid number [message #272876] Mon, 08 October 2007 01:55 Go to next message
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 #272881 is a reply to message #272876] Mon, 08 October 2007 02:09 Go to previous messageGo to next message
Maaher
Messages: 7065
Registered: December 2001
Senior Member
TO_NUMBER can only convert valid numeric values. So you might want to rethink your order by. Do you want to sort on ALL values, or do you want to strip non-numeric characters from your column? It's your call...

MHE
Re: ORA-01722: invalid number [message #272885 is a reply to message #272876] Mon, 08 October 2007 02:15 Go to previous messageGo to next message
rzkhan
Messages: 370
Registered: March 2005
Senior Member
In some situations. I need only the numeric values (of this field) to be sorted.
Could I do so?

Re: ORA-01722: invalid number [message #272887 is a reply to message #272885] Mon, 08 October 2007 02:17 Go to previous messageGo to next message
vamsi kasina
Messages: 2112
Registered: October 2003
Location: Cincinnati, OH
Senior Member
You may need to pad with zeroes and order by as a character only.

By
Vamsi
Re: ORA-01722: invalid number [message #272888 is a reply to message #272876] Mon, 08 October 2007 02:20 Go to previous messageGo to next message
Michel Cadot
Messages: 68704
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This question has been asked several times during the last months.
Search you can find many answers.

Regards
Michel
Re: ORA-01722: invalid number [message #273283 is a reply to message #272876] Tue, 09 October 2007 18:32 Go to previous message
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
Previous Topic: Identifying parent data source in downstream application
Next Topic: USING IN with Variable
Goto Forum:
  


Current Time: Tue Nov 05 13:31:15 CST 2024