Oracle's to_number - What's wrong?

Question

Hey guys,

What is wrong with the below format specifier for to_number?

SELECT TO_NUMBER('0,22', '0,99') * 100 FROM DUAL;

The result is 2200 instead of 22 -- what am I doing wrong?

Answer 1

Try:

TO_NUMBER('0,22', '9D99')

Unlike a literal comma, the D will only match the decimal separator. So if this fails, your decimal separator is probably a ., not a ,.

You can use this command to see the decimal separator and the thousand separator:

select value from nls_session_parameters
where parameter = 'NLS_NUMERIC_CHARACTERS'

If this returns .,, the comma is your thousand separator.

To change the separator for a single query, you could:

select TO_NUMBER('0,22','9D99','nls_numeric_characters=,.') from dual;

Answer 2

A quick guess: 0.22 versus 0,22 (same for 9.99 versus 0,99 )

?