informática

How to avoid ORA-01722 within a select and not using PL-SQL

Sometimes we need to use to_number over uncertain data types.
The "perfect" solution should be a PL-SQL function like "is_number", but it's not always possible.

So, here is a simple solution:

select
       case
               when UPPER(columna)=LOWER(columna)
                 then to_number(columna)
            when UPPER(columna)!=LOWER(columna)
                 then null
        end as converted_number
    from tabla_oracle
Submitted by admin on Fri, 2008/01/04 - 13:20. categories [ ]