SQL to_char Currency Formatting
Solution 1
If you want to do it in the query:
SELECT TO_CHAR(10000,'L99G999D99MI',
'NLS_NUMERIC_CHARACTERS = ''.''''''
NLS_CURRENCY = ''$'' ') "Amount"
FROM DUAL;
Gives $10'000.00
(as this string is getting pre-processed there are pairs of quotes around the characters (becoming single) and then to get a single-quote in the string you need four quotes to become one!)
SELECT TO_CHAR(10000,'L99G999D99MI',
'NLS_NUMERIC_CHARACTERS = '', ''
NLS_CURRENCY = ''$'' ') "Amount"
FROM DUAL;
Gives $10 000,00
Solution 2
This can be used as well since the decimal notation is already know for French countries
SELECT TO_CHAR(1000000000,'999,999,999,999.99') "Amount" FROM DUAL;
Solution 3
As one of the options, you can set NLS_TERRITORY
parameter at a session level:
alter session set nls_territory='FRANCE';
select to_char(10000, 'fm99G999D00') as french
from dual;
Result:
FRENCH
----------
10 000,00
alter session set nls_territory='SWITZERLAND';
select to_char(10000, 'fm99G999D00') as switzerland
from dual
Result:
SWITZERLAND
-----------
10'000.00
Law
Updated on January 18, 2021Comments
-
Law over 3 years
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 the output:
$10,000.00
.
Now, I want to convert the currency into a France currency, which the desire output is10 000,00
or a Switzerland currency with output10'000.00
. So, I modified the code as shown below for both of the case above:SELECT TO_CHAR(10000,'L99G999D99MI', 'NLS_NUMERIC_CHARACTERS = ''"", ""'' NLS_CURRENCY = ''$'' ') "Amount" FROM DUAL; SELECT TO_CHAR(10000,'L99G999D99MI', 'NLS_NUMERIC_CHARACTERS = ''". "'' NLS_CURRENCY = ''$'' ') "Amount" FROM DUAL;
But this code does not work and showing an error of
ORA-12702
. Is there any problem with the code? -
Law over 9 yearsThank you for the guidance!
-
Ravi.Kumar over 6 yearsif value passed in first argument is 0 then output is coming as '.00'. The leading zero is missing. Any fix for that?
-
Sarath Chandra almost 6 years@Ravi.Kumar: Try using "990.99" in the end for trailing zeros.
-
Saqib Ahmed almost 6 yearsGood example working fine but when I am using large amount value eg: 124500.21, it returns ##########.
-
Nick Krasnov almost 6 years@SaqibAhmed Simply change format string to fit your largest value. For example
fm999G999D00