Search code examples
oracle-databaseoracle12c

Convert VARCHAR to NUMBER with decimal on Oracle

I'm having some trouble to convert VARCHAR2 informations, like:

  • -111.21
  • 11.11
  • -51.146610399175472

to NUMBER. I want to store these numbers on a NUMBER(19,16) column. Mostly of these values are coordinates (latitude and longitude).

I already tried different commands with different values:

select cast('-111.21' as NUMBER) from dual
select cast('-111.21' as decimal) from dual
select cast('111.21' as decimal) from dual
select to_number('-1.1') from dual
select to_decimal('-1.1') from dual

But I always receive the error:

The specified number was invalid

This SQL:

select to_number('-134.33','099.99') from dual;

Works, but any change on the number (like change to '-34.33') return the same error.

What I'm doing wrong here? Obviously I'm missing something here but I can't figure out what.

Solution

  • I found the problem. I need to pass a mask as parameter to the to_number function. Like '999.999999999999999'

    So:

    select to_number('90.79493','999.999999999999999') from dual;
select to_number('90.146610399175472','999.999900000000000') from dual;
select to_number('90.34234324','999.999999999999999') from dual;

    works for different size of numbers.