Oracle TO_CHAR(number) Examples

Oracle TO_CHAR(number) Examples

Written by
Vinish Kapoor is a seasoned software development professional and a fervent enthusiast of artificial intelligence (AI). His impressive career spans over 20 years, marked by a relentless pursuit of innovation and excellence in the field of information technology. As an Oracle ACE, Vinish has distinguished himself as a leading expert in Oracle technologies, a title awarded to individuals who have demonstrated their deep commitment, leadership, and expertise in the Oracle community.
  • SQL
  • 3 mins read

This tutorial explains how to convert a number to string in Oracle using TO_CHAR(number) function. The following are the Oracle TO_CHAR(number) examples with syntax and format specifier details.

Syntax

TO_CHAR(number, format, [nlsparam])

Format Specifiers

Format ModelDescription
CC, SCCCentury (S prefixes BC dates with a minus sign)
YYYY, SYYYYYear (S prefixes BC dates with a minus sign)
IYYYYear based on ISO standard
YYY, YY, YLast three, two or one digits of the year
IYY, IY, ILast three, two or one digits of the ISO year
Y,YYY(Four Y's with comma) put a comma in the year (1,995)
YEAR, SYEARYear spelled out (S prefixes BC dates with a minus sign)
RRLast two digits of year in another century (allows for year 2000)
BC, ADBC or AD indicator
B.C., A.D.BC or AD indicators with periods
QNumeric quarter of the year (1-4 with Jan-Mar=1)
MM2 number month (Jan = 01)
RMRoman numeral month
MONTHName of month spelled out (upper case - month is upper case)
MONabbreviated name of month (upper case - month is upper case)
WWWeek of the year (1-53)
IWWeek of the year (1-52, 1-53) based on ISO standard
WWeek of month (1-5)
DDDday of year (1-366) (Don't forget leap years)
DDday of month (1-31)
Dday of week (1-7)
DAYName of day (upper case, day is upper case)
DYAbbreviated name of day
JJulian day (Number of days since January 1, 4712 BC)
AM,PMMeridian indicator
A.M., P.M.Meridian indicator with periods.
HH, HH12Hour of day (0-12)
HH24Use 24 hour clock for hours (1-24)
MIMinute (0-59)
SSSecond (0-60)
SSSSS(five S's) seconds past midnight. (0-86399)
NoneDate must be in the format 'DD-MON-YY';

Oracle TO_CHAR(number) Examples

1. The following example is to convert a number to leading zeros if less than 10 digits.

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

Output

0000012345

2. Leading a dollar sign example.

SELECT TO_CHAR (12345, '$99999') FROM DUAL;

Output

$12345

3. Convert to Decimal character example.

SELECT TO_CHAR (12345, '99999D99') FROM DUAL;

Output

12345.00

4. Convert to ISO Currency example.

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

Output

USD12345

5. Convert to Comma example.

SELECT TO_CHAR (12345, '99,999') FROM DUAL;

Output

12,345

6. Convert to Roman example.

SELECT TO_CHAR (12, 'RN') FROM DUAL;

Output

XII

See also: