ora-01722-"Invalid Number"

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
 
neethasanandAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

andrewstCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
rmyufaCommented:
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
neethasanandAuthor Commented:
thanks..
 there was some invalid data...
 
    neetha
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.