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: 345
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: 7062
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: 345
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: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
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: 64151
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: 2101
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: Fri Dec 09 23:07:29 CST 2016

Total time taken to generate the page: 0.05195 seconds