Link to home
Start Free TrialLog in
Avatar of neethasanand
neethasanand

asked on

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
 
ASKER CERTIFIED SOLUTION
Avatar of andrewst
andrewst
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of rmyufa
rmyufa
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
Avatar of neethasanand
neethasanand

ASKER

thanks..
 there was some invalid data...
 
    neetha