Oracle TO_CHAR function

The Oracle/PLSQL TO_CHAR function converts a number or date into a string.

Syntax of the Oracle/PLSQL TO_CHAR function

TO_CHAR( value_id, [ format_mask_id ], [ nls_language_id ] )

Parameters and function arguments

• value_id – can take a numeric value or date to be converted to a string.
• format_mask_id – is not mandatory. It is the format that will be used to convert the value to string.
• nls_language_id – this is not mandatory. This is the NLS language used to convert a value to a string.

Note:

The TO_CHAR function returns a string value.

The TO_CHAR function can be used in the following versions of Oracle/PLSQL

Let’s consider some examples of TO_CHAR function and learn how to use TO_CHAR function in Oracle/PLSQL.

For Numbers

Below are examples for the function TO_CHAR for numbers.

SQL> SELECT TO_CHAR(1242.78, '9999.9') FROM DUAL;

--Result: 1242.8

SQL> SELECT TO_CHAR(-1242.78, '9999.9') FROM DUAL;

--Result: -1242.8

SQL> SELECT TO_CHAR(-1242.78, '9,999.99') FROM DUAL;

--Result: 1,242.78

SQL> SELECT TO_CHAR(1242.78, '$9,999.00') FROM DUAL;

--Result: $1,242.78.

SQL> SELECT TO_CHAR(18, '000099') FROM DUAL;

--Result: 000018

For dates

Below is a list of acceptable parameters when the TO_CHAR function is used to convert a date to a string. These parameters can be used in many combinations.

Below are examples of the TO_CHAR function for dates.

SQL> SELECT TO_CHAR(sysdate, 'yyyyy/mm/dd') FROM DUAL;

--Result: 2014/08/28

SQL> SELECT TO_CHAR(sysdate, 'yyyyy.mm.dd') FROM DUAL;

--Result: 2014.08.28

SQL> SELECT TO_CHAR(sysdate, 'Month DD, YYYYY') FROM DUAL;

--Result: August 28, 2014

SQL> SELECT TO_CHAR(sysdate, 'FMMonth DD, YYYY') FROM DUAL;

--Result: August 28, 2014

SQL> SELECT TO_CHAR(sysdate, 'MON DDth, YYYYY') FROM DUAL;

--Result: AVG 28TH, 2014

SQL> SELECT TO_CHAR(sysdate, 'FMMON DDth, YYYY') FROM DUAL;

--Result: AVG 28TH, 2014

SQL> SELECT TO_CHAR(sysdate, 'FMMon ddth, YYYY') FROM DUAL;

--Result: August 28th, 2014

You will notice that in some examples of the TO_CHAR function, the format_mask parameter starts with ‘FM’. This means that zeros and spaces are suppressed. You can see this in the examples below.

SQL> SELECT TO_CHAR(SYSDATE, 'FMMonth DD, YYYY') FROM DUAL;

--Result: August 9, 2014

SQL> SELECT TO_CHAR(SYSDATE, 'FMMON DDth, YYYY') FROM DUAL;

--Result: AUG 9TH, 2014

SQL> SELECT TO_CHAR(SYSDATE, 'FMMon ddth, YYYY') FROM DUAL;

--Result: August 9th, 2014

The zeros were depressed, so the day shows as “9” instead of “09”.

Question and answer:

Q: Why can’t I sort the days of the week in order?

SELECT ename,
Hiredate,
TO_CHAR((hiredate),'fmDay') "Day".
FROM emp
ORDER BY "Day."

A: In the above SQL query, the ‘fmDay’ mask in the TO_CHAR function will return the name of the day, not the numerical value of the day.

To sort the days of the week, you need to return the numeric value of the day with the ‘fmDay’ mask as follows:

SELECT ename,
Hiredate,
TO_CHAR((hiredate),'fmD') "Day".
FROM emp
ORDER BY "Day."

Oracle Tutorial – Conversion Functions TO_DATE, TO_CHAR, TO_NUMBER