ISNUMBER

If you have a text field that you want to convert to a number that has a non numeric value in it, then the conversion will fail with an invalid number error. What you need is a function that determines if the string is a number, so that you can decide whether to convert or not. Sadly Oracle SQL doesn't have one.

All is not lost however, with a cunning use of translate you can suss out if a string is a number or not.

Let's say you wanted to convert JOBCODE in JOB into a number when it is numeric, and zero when it is not. The following SQL will do this for you quite nicely.

select distinct JOBCODE,
                ( case
                    when translate(JOBCODE, '_0123456789', '_') is null then to_number(JOBCODE)
                    else                                                     0
                 end )
  from PS_JOB

The Spirit Of The Age

The following SQL can be used to detemine an Employee's current age based on their birthdate.

select PER.EMPLID,
       PER.BIRTHDATE,
       case
         when TO_CHAR(PER.BIRTHDATE,'MMDD') <= TO_CHAR(SYSDATE,'MMDD')
          then TO_NUMBER(TO_CHAR(SYSDATE,'YYYY')) - TO_NUMBER(TO_CHAR(PER.BIRTHDATE,'YYYY'))
          else TO_NUMBER(TO_CHAR(SYSDATE,'YYYY')) - TO_NUMBER(TO_CHAR(PER.BIRTHDATE,'YYYY')) - 1
       end as AGE
  from PS_PERSONAL_DATA PER

Feel free to replace SYSDATE with whatever date you want to calculate the ages at. That's the spirit of the age...