CODEWITHSUNDEEP
sqloracle-database
nightfox79
nightfox79

Reputation: 2119

How to display a number with a space between currency and the number

I can't find a way to display a number with a space between the currency and the number. If I use the follow syntax:

select to_char(99.90,'FML90D99') from dual;

I get the following result:

$99.9

However the result I need is with a space between the dollar sign and the number

$ 99.9

How can I achieve this? If I use the following you get an error

select to_char(99.90,'FML 90D99') from dual;
ORA-01481: invalid number format model

Upvotes: 4

Views: 1249

Answers (2)

Aleksej
Aleksej

Reputation: 22949

If you don't want to hardcode the currency in your code, you can use:

SQL> select to_char(0,'FML') || ' ' || to_char(99.90,'FM90D99') from dual;

TO_CHAR(0,'FML')||
------------------
$ 99,9

This even works if you have a different currency:

SQL> alter session set nls_currency = '£';

Session altered.

SQL> select to_char(0,'FML') || ' ' || to_char(99.90,'FM90D99') from dual;

TO_CHAR(0,'FML')||
------------------
£ 99,9

Upvotes: 2

Roeland Van Heddegem
Roeland Van Heddegem

Reputation: 1735

You can use this (by defining your currency):

SELECT to_char(800, 'FML999G990D00', 'NLS_CURRENCY=''$ ''') FROM dual;

Upvotes: 4

Related Questions