Oracle / PLSQL: TO_CHAR Function

This article is written about how to use the Oracle/PLSQL TO_CHAR feature with syntax and examples.

Description

The Oracle/PLSQL TO_CHAR characteristic converts a number or date to a string.

Syntax

The syntax for the TO_CHAR feature in Oracle/PLSQL is:

TO_CHAR( value [, format_mask] [, nls_language] )

Parameters or Arguments

value A number or date that will be converted to a string. format_mask Optional. This is the structure that will be used to convert value to a string. nls_language Optional. This is the nls language used to convert cost to a string.

Returns

The TO_CHAR function returns a string value.

Applies To

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

Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i

Example

Let’s look at some Oracle TO_CHAR characteristic examples and explore how to use the TO_CHAR function in Oracle/PLSQL.

Examples with Numbers

For example:

The following are wide variety examples for the TO_CHAR function.

TO_CHAR(1210.73, '9999.9')
Result: ' 1210.7'

TO_CHAR(-1210.73, '9999.9')
Result: '-1210.7'

TO_CHAR(1210.73, '9,999.99')
Result: ' 1,210.73'

TO_CHAR(1210.73, '$9,999.00')
Result: ' $1,210.73'

TO_CHAR(21, '000099')
Result: ' 000021'

Examples with Dates

The following is a listing of legitimate parameters when the TO_CHAR characteristic is used to convert a date to a string. These parameters can be used in many combinations.

Parameter Explanation YEAR Year, spelled out YYYY 4-digit year YYY YY Y Last 3, 2, or 1 digit(s) of year. IYY IY I Last 3, 2, or 1 digit(s) of ISO year. IYYY 4-digit yr based on the ISO standard Q Quarter of 12 months (1, 2, 3, 4; JAN-MAR = 1). MM Month (01-12; JAN = 01). MON Abbreviated identify of month. MONTH Name of month, padded with blanks to size of 9 characters. RM Roman numeral month (I-XII; JAN = I). WW Week of year (1-53) where week 1 starts offevolved on the first day of the year and continues to the seventh day of the year. W Week of month (1-5) the place week 1 starts on the first day of the month and ends on the seventh. IW Week of 12 months (1-52 or 1-53) primarily based on the ISO standard. D Day of week (1-7). DAY Name of day. DD Day of month (1-31). DDD Day of year (1-366). DY Abbreviated name of day. J Julian day; the range of days on account that January 1, 4712 BC. HH Hour of day (1-12). HH12 Hour of day (1-12). HH24 Hour of day (0-23). MI Minute (0-59). SS Second (0-59). SSSSS Seconds previous nighttime (0-86399). FF Fractional seconds.

The following are date examples for the TO_CHAR function.

TO_CHAR(sysdate, 'yyyy/mm/dd')
Result: '2003/07/09'

TO_CHAR(sysdate, 'Month DD, YYYY')
Result: 'July 09, 2003'

TO_CHAR(sysdate, 'FMMonth DD, YYYY')
Result: 'July 9, 2003'

TO_CHAR(sysdate, 'MON DDth, YYYY')
Result: 'JUL 09TH, 2003'

TO_CHAR(sysdate, 'FMMON DDth, YYYY')
Result: 'JUL 9TH, 2003'

TO_CHAR(sysdate, 'FMMon ddth, YYYY')
Result: 'Jul 9th, 2003'

You will observe that in some TO_CHAR feature examples, the format_mask parameter starts with “FM”. This capability that zeros and blanks are suppressed. This can be viewed in the examples below.

TO_CHAR(sysdate, 'FMMonth DD, YYYY')
Result: 'July 9, 2003'

TO_CHAR(sysdate, 'FMMON DDth, YYYY')
Result: 'JUL 9TH, 2003'

TO_CHAR(sysdate, 'FMMon ddth, YYYY')
Result: 'Jul 9th, 2003'

The zeros have been suppressed so that the day element suggests as “9” as hostile to “09”.

Frequently Asked Questions

Question: Why does not this type the days of the week in order?

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

Answer: In the above SQL, the fmDay layout masks used in the TO_CHAR function will return the title of the Day and not the numeric value of the day.

To kind the days of the week in order, you want to return the numeric price of the day via using the fmD layout mask as follows:

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