?
Solved

ora-01722-"Invalid Number"

Posted on 2003-03-29
3
Medium Priority
?
1,468 Views
Last Modified: 2007-12-19
I have a table which contains a varchar2 field. This field contains numeric values from 7th letter onwards(eg.1CBCCV1024). i need to find the maximum value of this field. I have tried the following query,

 select max(to_number(substr(fieldname,7))) from tablename;
 
  ERROR at line 1:
  ORA-01722: invalid number

thanks
 
0
Comment
Question by:neethasanand
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 15

Accepted Solution

by:
andrewst earned 225 total points
ID: 8233520
There must be some values in the table that are not in the valid format.  You can find them like this:

select *
from tablename
where translate(substr(fieldname,7),'x0123456789','x') is not null;

If they can't be fixed, you should be able to exclude them from your query like this:

select max(to_number(code))
from
( select substr(fieldname,7) code
  from   tablename
  where  translate(substr(fieldname,7),'x0123456789','x') is null
);
0
 
LVL 1

Expert Comment

by:rmyufa
ID: 8233963
Hi!

Probe with this:

select max(to_number(substr(lPAD(fieldname, 7, ' '),7))) from tablename;

May be not all values are 7 chars long or you have Null values in this column. Verify availability of Null values or shorter data with this:

SELECT MIN(LENGTH(NVL(fieldname, '.')) FROM tablename;

T=Regards,
pm
0
 

Author Comment

by:neethasanand
ID: 8236661
thanks..
 there was some invalid data...
 
    neetha
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
Suggested Courses

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question