Convert string to decimal Oracle database

Question

I intended convert data type string to decimal in Oracle data base and i get this error:

ORA-01722: número no válido 01722. 00000 - "invalid number" *Cause: The specified number was invalid. *Action: Specify a valid number

Code:

TO_NUMBER('5.0') AS numero

CAST('5.0' AS NUMBER) AS numero

Answer 1

The code you have posted works fine for me.

However, check you NLS/locale settings. If I change mine to use something other that '.' as the decimal separator then I get the same error.

So it would appear that it may be down to your locale settings. You can check with select * from v$nls_parameters to see what the nls_numeric_characters is set to.

Answer 2

select TO_NUMBER('5.0') from dual;

select NLS_NUMERIC_CHARACTERS
 from v$nls_parameters;

 --There must be '.' in value filed to run above query

Answer 3

thanks for your answeres, it served as a guide to find the root of the problem. verifying the local configuration of nls_numeric_characters encontre ',.' Apparently the server configuration is different and therefore caused the error in the Query.

To give solution, replace '.' by ',' I assumed the configuration of nls_numeric_characters on the server is with ','.

select TO_NUMBER(REPLACE('5.0','.',',')) from dual;