Customize SQL query output

You can use TO_CHAR(number) by passing the correct values for the parameter: fmt .

Syntax:

TO_CHAR(n [, fmt [, 'nlsparam' ] ])

Example, with decimal:

SELECT TO_CHAR(23000,'L99G999D99') FROM DUAL;

Output: R$23.000,00

Example, without decimal:

SELECT TO_CHAR(23000,'L99G999') FROM DUAL;

Output: R$23.000

Explaining:

fmt : Must be the desired format for the conversion, you can see all options here , where:

• L : Returns the currency symbol set for your bank ( NLS_CURRENCY ).
• G : Returns the group separator set for your bank ( NLS_NUMERIC_CHARACTER ).
• D : Returns the decimal separator set for your bank ( NLS_NUMERIC_CHARACTER ).

Note that the number 9 in the format refers to the number of digits that your mask will share, in which case the maximum value will be: R$99.999,99 with decimal and R$99.999 without decimal. >

Update:

If your database is set to a different pattern than you want, you can change the value of the properties NLS_CURRENCY and NLS_NUMERIC_CHARACTER directly to TO_CHAR , using the nlsparam parameter, example:

SELECT TO_CHAR(23000,'L99G999', 'NLS_NUMERIC_CHARACTERS = '',.'' NLS_CURRENCY = ''R$'' ') FROM DUAL;

As you can see in this example the default SQL Fiddle is American and in this another example , changing the parameters to the pattern you want.