Number format models in Oracle SQL

Read this Oracle Tutorial Point article to know more about Number formatting in Oracle SQL. A formatting model is a character literal that describes the format of Number data stored in a character string.

D à 99D99: It returns the specified position of the decimal character. Period(.) is the default decimal delimiter. Only one decimal character can be specified in a number format model.

Examples:

SQL> SELECT TO_CHAR(1234,'99999D99') FROM DUAL;
SQL> SELECT TO_CHAR(1234,'999D99') FROM DUAL;

EEE à 9.9EEEE: Returns a value using scientific notation.

Examples:

SQL> SELECT TO_CHAR(1234,'9.9EEEE'), TO_CHAR(1234,'9.99EEEE') FROM DUAL;

G à 9G999: Returns the specified position of the group separator. Multiple group separators can be specified.

Examples:

SQL> SELECT TO_CHAR(12345678,'99G99G999') FROM DUAL;
SQL> SELECT SAL, TO_CHAR(SAL,'99G999G99') FROM EMP;

L à L999: Returns the specified position of the local currency symbol.

Examples:

SQL> SELECT TO_CHAR(123456,'L99999') FROM DUAL;
SQL> SELECT SAL,TO_CHAR(1234,'L9999999') CURRENCY FROM EMP WHERE DNO=10;
SQL> SELECT SAL,TO_CHAR(SAL,'L99G999D99','NLS_CURRENCY=IndRupees') SALARY FROM EMP WHERE DNO=20;

MI à 9999MI: Returns negative value with a trailing minus sign (-). Return positive value with trailing blank. The MI format should be declared as trailing argument only.

Examples:

SQL> SELECT TO_CHAR(-1000, 'L99G999D99MI') FROM DUAL;
SQL> SELECT SAL,COMM_SAL,COMM,TO_CHAR(COMM-SAL,'L99999MI') FROM EMP WHERE DNO IN (10,20,30);

PR à 999PR: Returns negative value in <angle Brackets>. This can appear only as trailing declaration.

Examples:

SQL> SELECT TO_CHAR(-1000, 'L99G999D99PR') FROM DUAL;
SQL> SELECT SAL,COMM_SAL,COMM,TO_CHAR(COMM-SAL,'L99999PR') FROM EMP WHERE DNO IN (10,20,30);

RN à Returns a value as Roman Number in Upper case.

rn à Returns a value as Roman Number in Lower case.

The value can be an integer between 1 and 9999.

Examples:

SQL> SELECT TO_CHAR(1000, 'RN'), TO_CHAR(1000,'rn') FROM DUAL;
SQL> SELECT TO_CHAR(SAL, 'RN'), TO_CHAR(SAL,'rn') FROM EMP;

S à S9999: Returns negative value with a leading minus sign and return positive value with a leading plus sign.

• 9999S : Returns negative value with minus sign at trailing. Return positive value with a trailing plus sign. S can appear as first or last value.

Examples:

SQL> SELECT TO_CHAR(1000,'S9999'), TO_CHAR(-1000,'S9999') FROM DUAL;
SQL> SELECT SAL,TO_CHAR(SAL,'9999S'), TO_CHAR(SAL,'S9999') FROM EMP;

SQL> SELECT TO_CHAR(1000,'9999S'),TO_CHAR(-1000,'9999S') FROM DUAL;

X à XXXX: Returns the hexadecimal value of the specified number of digits. If the number is not an integer, Oracle rounds it to an integer. Accept only positive values or 0.

Examples:

SQL> SELECT TO_CHAR(1000,’XXXX’) FROM DUAL;
SQL> SELECT SAL,TO_CHAR(SAL,’XXXX’) FROM EMP;

Comma(,) à 9,999: Returns a comma in the specified position. Multiple commas can be specified.

Examples:

SQL> SELECT TO_CHAR(10000,'99,999D99') FROM DUAL;
SQL> SELECT SAL,TO_CHAR(SAL,'99,999D99') FROM DUAL;

Period(.) à99.99: returns a decimal point, at the specified position. Only one period can be specified in a number format model.

Examples:

SQL> SELECT TO_CHAR(10000,'L99,999.99') FROM DUAL;
SQL> SELECT TO_CHAR(SAL,'L99,999.99') FROM EMP;

$ à $9999: Return value with a leading Dollar sign.

Examples:

SQL> SELECT TO_CHAR(10000,'$99,999.99') FROM DUAL;
SQL> SELECT SAL,TO_CHAR(SAL,'$9,99,999.99') FROM EMP;

0 (ZERO) à 0999: Returns leading zeros. 9990: returns trailing zero.

Examples:

SQL> SELECT TO_CHAR(1000,'0999999'), TO_CHAR(1000,'09999990') FROM DUAL;
SQL> SELECT SAL, TO_CHAR(SAL,'$099,999.99') FROM EMP;

9 à 9999: Return value with a specified number of digits with a leading space when positive or leading minus when negative.

Examples:

SQL> SELECT TO_CHAR(1000-600,'99999'), TO_CHAR(600-1000,'99999') FROM DUAL;
SQL> SELECT TO_CHAR(20.25-20,'99999') FROM DUAL;

C à C9999: returns specified position of the ISO Currency symbol.

Examples:

SQL> SELECT TO_CHAR(1000,'C9999.99') FROM DUAL;
SQL> SELECT SAL, TO_CHAR(SAL,'C99999.99') FROM EMP;