Customize SQL query output

Question

How do I customize an output of an example SQL query resulting from a sum of the columns OUTPUT: 23000 wanted to customize only this output to R $ 23.000

Answer 1

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

Syntax:

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

Example, with decimal:

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

Exit: R$23.000,00

Example, without decimal:

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

Exit: R$23.000

---

Explaining:

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

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

Remembering, that the number 9 present in the format, refers to the number of digits that its mask comparta, in this case, the maximum value will be: R$99.999,99 decimally and R$99.999 decimal-free.

---

Update:

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

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

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