Oracle TO_CHAR Function Explained with Examples

The Oracle TO_CHAR function is one of the most common and useful string manipulation functions in Oracle. In this article and video, I’ll explain how to use the TO_CHAR function.

 

Purpose of the Oracle TO_CHAR Function

The purpose of the Oracle TO_CHAR function is to convert either a number or a date value to a string value. It works similar to the TO_DATE function and TO_NUMBER function. This function takes either a number or a date as an input, and converts it to a string value to be displayed or processed accordingly. The converted data is a VARCHAR2 data type.

The input value can be a numeric type of NUMBER, BINARY_FLOAT, or BINARY_DOUBLE, or it can be a date type of DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, or TIMESTAMP WITH LOCAL TIME ZONE.

This function also exists in PostgreSQL.

 

Syntax

The syntax of the Oracle TO_CHAR function is:

TO_CHAR( input_value, [format_mask], [nls_parameter] )

 

Parameters

The parameters of the TO_CHAR function are:

• input_value (mandatory): This is the value to convert into a String type. This is the main input to the function, and this value can either be a DATE type or any of the Number types.
• format_mask (optional): This is the format that the input_value should be displayed as. If this is omitted, the function will use a default format, as mentioned below.
• nls_parameter (optional): This value is used to determine how the output value is displayed.

If the format_mask parameter is omitted, then:

• DATE values are converted to values in the default date format.
• TIMESTAMP and TIMESTAMP WITH LOCAL TIME ZONE values are converted to values in the default timestamp format.
• TIMESTAMP WITH TIME ZONE values are converted to values in the default timestamp with time zone format.
• Numeric values are converted to a string value that is long enough to hold its significant digits.

While you’re here, if you want an easy-to-use list of the main features in Oracle SQL, get my SQL Cheat Sheet here:

 

Format Mask

The format mask is helpful as it allows you to specify what format the input_value should be converted to.

To make it clear, we need to specify some values for the format mask. The possible values are:

Year

Parameter	Explanation
YEAR	Year, spelled out in full words
YYYY	4-digit year
YYY	Last 3 digits of year
YY	Last 2 digits of year
Y	Last digit of year
IYY	Last 3 digits of ISO year
IY	Last 2 digits of ISO year
I	Last digit of ISO year
IYYY	4-digit year, which is based on the ISO standard
RRRR	This format accepts a 2-digit year, and returns a 4-digit year. If the provided value is between 0 and 49, it will return a year greater than or equal to 2000. If the provided value is between 50 and 99, it will return a year less than 2000

 

Month

Parameter	Explanation
Q	Quarter of year, from 1 to 4. JAN to MAR = 1
MM	Month, from 01 to 12. JAN = 01
MON	Abbreviated name of month.
MONTH	Name of month, padded with blanks to length of 9 characters.
RM	Roman numeral month, from I to XII. JAN = I.

 

Week

Parameter	Explanation
WW	Week of year, from 1 to 53. Week 1 starts on the first day of the year, and continues to the seventh day of the year.
W	Week of month, from 1 to 5. Week 1 starts on the first day of the month and ends on the seventh.
IW	Week of year, from 1 to 52 or 1 to 53, based on the ISO standard.

 

Day

Parameter	Explanation
D	Day of week, from 1 to 7.
DAY	Name of day.
DD	Day of month, from 1 to 31.
DDD	Day of year, from 1 to 366.
DY	Abbreviated name of day.
J	Julian day, which is the number of days since January 1, 4712 BC.

 

Time

Parameter	Explanation
HH	Hour of day, from 1 to 12.
HH12	Hour of day, from 1 to 12.
HH24	Hour of day, from 0 to 23.
MI	Minute, from 0 to 59
SS	Second, from 0 to 59
SSSSS	Seconds past midnight, from 0 to 86399.
FF	Fractional seconds. This uses a value from 1 to 9 after FF, to indicate the number of digits in the fractional seconds (e.g. FF7)

 

Indicators

Parameter	Explanation
AM, A.M., PM, or P.M.	Meridian indicator
AD or A.D	AD indicator
BC or B.C.	BC indicator
TZD	Daylight savings information
TZH	Time zone hour.
TZM	Time zone minute.
TZR	Time zone region.

 

The format mask also means that punctuation and quoted text is reproduced in the result. So, if you add an underscore (_) or a dash (-), for example, it is shown in the output.

NLS_DATE_LANGUAGE Explanation

The NLS_DATE_LANGUAGE can be specified in this function to determine how the date is shown. As you may know, different regions show the same date in different ways:

• China: yyyy-mm-dd
• UK: dd/mm/yyyy
• US: mm/dd/yyyy

To use the NLS_DATE_LANGUAGE as part of this function, you can use any language listed under Table A-1 on the Oracle Language page. Some examples are American, English, French, German, and Swedish.

 

Examples of the TO_CHAR Function

Here are some examples of the TO_CHAR function. I find that examples are the best way for me to learn about code, even with the explanation above.

Example 1

This example converts the number 12345.67 into the format specified in the second parameter.

SELECT TO_CHAR(12345.67, '99999.9') FROM DUAL;

Result:

12345.7

As you can see, the result only has one decimal place. This is because the output format only had one decimal place, and the .67 was rounded up to .7.

 

Example 2

This next example converts the number 12345 into a string with zeroes in it.

SELECT TO_CHAR(12345, '00000000') FROM DUAL;

Result:

00012345

This is the result because the format mask of 0 means that there are leading zeroes added (to the left of the number) when it is converted.

 

Example 3

This example converts the current date (SYSDATE) into a string, using a format of 4 digit year, then an underscore, then 2 digit month, underscore, then two digit day.

SELECT TO_CHAR(SYSDATE, 'YYYY_MM_DD') FROM DUAL;

Result:

2014_12_27

 

Example 4

This example converts the SYSDATE value again, but uses the format mask of “Month” which is the full month name.

SELECT TO_CHAR(SYSDATE, 'Month') FROM DUAL;

Result:

December

This is because the Month of SYSDATE is December, and that is what gets converted.

 

Similar Functions

Some functions which are similar to the TO_CHAR function are:

• TO_DATE – converts a string value to a DATE type.
• TO_NUMBER – converts a value to a NUMBER type
• CAST – used for many types, including dates

You can find a full list of Oracle functions here.

While you’re here, if you want an easy-to-use list of the main features in Oracle SQL, get my SQL Cheat Sheet here: