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.

Advertisement

Syntax

TO_CHAR(number, format, [nlsparam])

Format Specifiers

Format Model Description
CC, SCC Century (S prefixes BC dates with a minus sign)
YYYY, SYYYY Year (S prefixes BC dates with a minus sign)
IYYY Year based on ISO standard
YYY, YY, Y Last three, two or one digits of the year
IYY, IY, I Last 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, SYEAR Year spelled out (S prefixes BC dates with a minus sign)
RR Last two digits of year in another century (allows for year 2000)
BC, AD BC or AD indicator
B.C., A.D. BC or AD indicators with periods
Q Numeric quarter of the year (1-4 with Jan-Mar=1)
MM 2 number month (Jan = 01)
RM Roman numeral month
MONTH Name of month spelled out (upper case - month is upper case)
MON abbreviated name of month (upper case - month is upper case)
WW Week of the year (1-53)
IW Week of the year (1-52, 1-53) based on ISO standard
W Week of month (1-5)
DDD day of year (1-366) (Don't forget leap years)
DD day of month (1-31)
D day of week (1-7)
DAY Name of day (upper case, day is upper case)
DY Abbreviated name of day
J Julian day (Number of days since January 1, 4712 BC)
AM,PM Meridian indicator
A.M., P.M. Meridian indicator with periods.
HH, HH12 Hour of day (0-12)
HH24 Use 24 hour clock for hours (1-24)
MI Minute (0-59)
SS Second (0-60)
SSSSS (five S's) seconds past midnight. (0-86399)
None Date must be in the format 'DD-MON-YY';
Related:  Translating Strings to Numeric Equivalents

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.

Advertisement
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.

Advertisement
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:

Vinish Kapoor

An Oracle Apex Consultant, Oracle ACE, and founder of foxinfotech.in and orclqa.com a question and answer forum for developers.