Oracle SQL - change decimal dot for a comma

Question

I'm on Brazil, and our currency format is something like 'R$ 9.999,00'.

I'm trying to select a field, and change the format of the return. However, it seems I can't do it. I've tried:

ALTER SESSION SET NLS_NUMERIC_CHARACTERS = ',.';

and

SELECT to_char(10,'9,999.00', 'NLS_NUMERIC_CHARACTERS = '',.''') FROM DUAL

None of those worked. Not sure if it might be my client (I'm on Ubuntu using sqldeveloper), or if I'm doing something wrong.

Any ideas?

Answer 1

Use

SELECT to_char(10,'9G990D00', 'NLS_NUMERIC_CHARACTERS = '',.''') FROM DUAL

G is symbol for thousands separator D is for decimal separator

Answer 2

I think that your format mask is wrong.Please try this format mask:

SELECT to_char(10000,'99G990D00', 'NLS_NUMERIC_CHARACTERS = '',.''') FROM DUAL

make sure you will use correct number of leading '9' in format mask for bigger numbers