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.
The syntax of the Oracle TO_CHAR function is:
TO_CHAR( input_value, [format_mask], [nls_parameter] )
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
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||Year, spelled out in full words|
|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|
|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.|
|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.|
|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.|
|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)|
|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.
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.
This example converts the number 12345.67 into the format specified in the second parameter.
SELECT TO_CHAR(12345.67, '99999.9') FROM DUAL;
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.
This next example converts the number 12345 into a string with zeroes in it.
SELECT TO_CHAR(12345, '00000000') FROM DUAL;
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.
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;
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;
This is because the Month of SYSDATE is December, and that is what gets converted.
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.
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
Image courtesy of digitalart / FreeDigitalPhotos.net