FB

Oracle FunctionThe 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.

 

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.

Get Your SQL Cheat Sheet

Download the SQL Cheat Sheets: common commands and syntax - to save you time.
You'll get them for Oracle, SQL Server, MySQL, and PostgreSQL.
Print them or use them as an easy reference.

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

ParameterExplanation
YEARYear, spelled out in full words
YYYY4-digit year
YYYLast 3 digits of year
YYLast 2 digits of year
YLast digit of year
IYYLast 3 digits of ISO year
IYLast 2 digits of ISO year
ILast digit of ISO year
IYYY4-digit year, which is based on the ISO standard
RRRRThis 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

ParameterExplanation
QQuarter of year, from 1 to 4. JAN to MAR = 1
MMMonth, from 01 to 12. JAN = 01
MONAbbreviated name of month.
MONTHName of month, padded with blanks to length of 9 characters.
RMRoman numeral month, from I to XII. JAN = I.

 

Week

ParameterExplanation
WWWeek 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.
WWeek of month, from 1 to 5. Week 1 starts on the first day of the month and ends on the seventh.
IWWeek of year, from 1 to 52 or 1 to 53, based on the ISO standard.

 

Day

ParameterExplanation
DDay of week, from 1 to 7.
DAYName of day.
DDDay of month, from 1 to 31.
DDDDay of year, from 1 to 366.
DYAbbreviated name of day.
JJulian day, which is the number of days since January 1, 4712 BC.

 

Time

ParameterExplanation
HHHour of day, from 1 to 12.
HH12Hour of day, from 1 to 12.
HH24Hour of day, from 0 to 23.
MIMinute, from 0 to 59
SSSecond, from 0 to 59
SSSSSSeconds past midnight, from 0 to 86399.
FFFractional 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

ParameterExplanation
AM, A.M., PM, or P.M.Meridian indicator
AD or A.DAD indicator
BC or B.C.BC indicator
TZDDaylight savings information
TZHTime zone hour.
TZMTime zone minute.
TZRTime 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.

 

Video

For an example of this function, watch the video here.

 

Lastly, if you enjoy the information and career advice I’ve been providing, sign up to my newsletter below to stay up-to-date on my articles. You’ll also receive a fantastic bonus. Thanks!

Get Your SQL Cheat Sheet

Download the SQL Cheat Sheets: common commands and syntax - to save you time.
You'll get them for Oracle, SQL Server, MySQL, and PostgreSQL.
Print them or use them as an easy reference.

Image courtesy of digitalart / FreeDigitalPhotos.net

Get Your SQL Cheat Sheets Now:

Want Some SQL Cheat Sheets?

sql-cheat-sheet-images-v2

Download the SQL Cheat Sheets: common commands and syntax - to save you time.

You'll get them for Oracle, SQL Server, MySQL, and PostgreSQL.

Print them or use them as an easy reference.