Re: Convert char to number ?

tjmxyz_at_my-deja.com wrote:
> I have a column markcol1
> I need to convert it to a number and sum it but I can't guarantee all
> values will be numbers..
>
> For example
> MARKCOL1
> -----------
> abc
> 10
> 0
> .NULL.
> 12
>
> IF I do...
> SELECT SUM(TO_NUMBER(markcol1)) from mytable;
>
> I get...
>
> ORA-01722: invalid number
>
> How do I ignore any character expressions....
>
> Tried also
> SELECT SUM(TO_NUMBER(NVL(markcol1,0))) from mytable;
>
> But of course that did not work...
>
> What do I use?

SELECT
   SUM(TO_NUMBER(DECODE( NVL( LENGTH( TRANSLATE(str, '!0123456789','!') ), 0)

                       , 0, str
                       , NULL))
      ) 
FROM (SELECT '123' AS str FROM dual
      UNION ALL
      SELECT '567' FROM dual
      UNION ALL
      SELECT NULL FROM dual
      UNION ALL
      SELECT '12ABC' FROM dual)

I hope it helps...
But it means to me you have very strange data structure... :-(

÷ÁÌÅÒÉÊ àÒÉÎÓËÉÊ

-- 
Oracle8 Certified DBA