ORACLE SQL – How to add commas and dollar sign to a table value

For this, I am trying to display my table values in a currency format, here’s my code;

SELECT SNAME,
        (SAL) AS MONTHLY,
        ROUND((SAL/22), 2) AS DAILY,
        ROUND(((SAL/22)/8), 2) AS HOURLY
        FROM STAFF
            WHERE BRANCHID = 30

This is what I get;

SNAME         MONTHLY      DAILY     HOURLY
---------- ---------- ---------- ----------
BATES            1800      81.82      10.23

This is what I want;

SNAME         MONTHLY      DAILY     HOURLY
---------- ---------- ---------- ----------
BATES            $1,800.00      $81.82      $10.23

So pretty much format the values with a ‘$’ sign and automatically add a ‘,’ where appropriate.

I am very new to this so I apologize if this sounds dumb in any way :p

Answer

This nature can be derived using the NLS setting.

You can alter the NLS_LANGUAGE and NLS_TERRITORY for your session and use the TO_CHAR function to achieve the desired behavior as follows:

SQL> alter session set NLS_LANGUAGE=AMERICAN;

Session altered.

SQL> alter session set NLS_TERRITORY=AMERICA;

Session altered.

SQL>
SQL> SELECT TO_CHAR(1800,'L99G999D00') salary FROM DUAL;

SALARY
--------------------
           $1,800.00

SQL>

You can see current NLS setting using following Views:

1. NLS_SESSION_PARAMETERS
2. NLS_DATABASE_PARAMETERS
3. NLS_INSTANCE_PARAMETERS