CODEWITHSUNDEEP
oracle-databaseoracle12c
Dherik
Dherik

Reputation: 19050

Convert VARCHAR to NUMBER with decimal on Oracle

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

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.

Upvotes: 2

Views: 13270

Answers (1)

Dherik
Dherik

Reputation: 19050

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.

Upvotes: 2

Related Questions