Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

ora-01722-"Invalid Number"

Posted on 2003-03-29
3
Medium Priority
?
1,488 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
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

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

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

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
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.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses

578 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