Oracle SQL - change decimal dot for a comma

oracle currency comma
Andre Cardoso picture Andre Cardoso · Oct 19, 2012 · Viewed 56.8k times · Source

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

Petr Pribyl picture Petr Pribyl · Oct 19, 2012

Use

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

G is symbol for thousands separator D is for decimal separator

Related questions

SQL to_char Currency Formatting

I am facing a to_char() currency formatting problem here. The below code is working for me: SELECT TO_CHAR(10000,'L99G999D99MI', 'NLS_NUMERIC_CHARACTERS = ''.,'' NLS_CURRENCY = $') "Amount" FROM DUAL; which will provide me with …

Read More
Get list of all tables in Oracle?

How do I query an Oracle database to display the names of all tables in it?

Read More
How do I limit the number of rows returned by an Oracle query after ordering?

Is there a way to make an Oracle query behave like it contains a MySQL limit clause? In MySQL, I can do this: select * from sometable order by name limit 20,10 to get the 21st to the 30th rows (skip the …

Read More