TO_CHAR ORACLE - Online Tech Support
  Home
 

This tutorial page is based on examples to be easier to follow. The Oracle TO_CHAR function allows converting others’ data types value to character type and function To_Char always returns VARCHAR2 type. You can convert to VARCHAR2 for example DATE, NUMBER, NCHAR, NVARCHAR2, CLOB, or NCLOB data types. The TO_CHAR syntax is following:

TO_CHAR(<datatype>[,<data_mask>])

Take a look at the examples below to see how works Oracle TO_CHAR. For example the first query is number “01234” without To_Char function and the nature of this type is getting trimmed by Oracle database so the SQL output returns “1234“.

SELECT 01234 
  FROM DUAL;

online tech support online computer help computer technician computer problems computer oracle to_char oracle to_char in oracle sql to_char oracle sql to_char in oracle sql database sql retirement planning retirement sql database programming database oracle retirement planning retirement sql database oracle database sql

The reason why Oracle database removed the first 0 (zero) is to keep the database tidy and that way the number looks less confusing for users. Having a zero in front of 1234 doesn’t make much sense either so it gets cleaned away.

Now to keep the number as with the original look, you should use Oracle To_Char function and store the value into Oracle database table as VARCHAR2 type because numbers like phone numbers or credit card numbers starting with 0 (zero) wouldn’t be valid after removing the first zero. We can’t treat all numbers as “numbers” since there are numbers that should keep their look to remain correct and valid.

The second example demonstrates how will stay a number treated as text and to keep its look. This is a demonstrational query so we wrote the number as text and To_Char function is not needed in this case but you can use this example with other datatype variables in place of ‘01234‘. Take a look at the following query and note that Oracle database left the first zero as is and your phone numbers wouldn’t be ruined using this VARCHAR2 type.

SELECT TO_CHAR('01234') 
  FROM DUAL;

online tech support online computer help computer technician computer problems computer oracle to_char oracle to_char in oracle sql to_char oracle sql to_char in oracle sql database sql retirement planning retirement sql database programming database oracle retirement planning retirement sql database oracle database sql

Also Oracle To_Char can be used to give numbers a certain look so they look better and more clear on a report or a bill. To format a number you would need to use a data mask and for numbers there are limited amount of key-letters. To see number formatting element take a look at the table below or this link will send you directly to Oracle webpage for possible mask elements. The mask key-letters should be used only with Oracle To_Char as on the following example. On this example we are filling in missing zeros after the coma point to have always two places after the decimal point.

SELECT TO_CHAR(111.2,'990D00') AS formatted_number
  FROM DUAL;

online tech support online computer help computer technician computer problems computer oracle to_char oracle to_char in oracle sql to_char oracle sql to_char in oracle sql database sql retirement planning retirement sql database programming database oracle retirement planning retirement sql database oracle database sql

Changing value from number to character is not as popular as turning it from a date type to text and the following part of this tutorial will continue with date values. To change a date look you would need to use a mask again but this time with different mask elements. For example the following Oracle To_Char example has a mask as year(YYYY)-month(MM)-day(DD) hours in 24 digits(HH24):minutes(MI):seconds(SS).

SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS') AS date_to_char
  FROM DUAL;

online tech support online computer help computer technician computer problems computer oracle to_char oracle to_char in oracle sql to_char oracle sql to_char in oracle sql database sql retirement planning retirement sql database programming database oracle retirement planning retirement sql database oracle database sql

Also here you can find a list of possible Oracle masks elements for a date type. Use those mask elements only with To_Char function.


See Also:
Oracle Select Oracle SUBSTR Oracle INSTR Oracle Replace Oracle Length Home

 

Number Format Elements Example Description
, (comma) 9,999 Returns a comma in the specified position. You can specify multiple commas in a number format model.Restrictions:

  • A comma element cannot begin a number format model.
  • A comma cannot appear to the right of a decimal character or period in a number format model.
. (period) 99.99 Returns a decimal point, which is a period (.) in the specified position.Restriction: You can specify only one period in a number format model.
$ $9999 Returns value with a leading dollar sign.
0 09999990 Returns leading zeros.Returns trailing zeros.
9 9999 Returns value with the specified number of digits with a leading space if positive or with a leading minus if negative. Leading zeros are blank, except for a zero value, which returns a zero for the integer part of the fixed-point number.
B B9999 Returns blanks for the integer part of a fixed-point number when the integer part is zero (regardless of zeros in the format model).
C C999 Returns in the specified position the ISO currency symbol (the current value of the NLS_ISO_CURRENCY parameter).
D 99D99 Returns in the specified position the decimal character, which is the current value of the NLS_NUMERIC_CHARACTER parameter. The default is a period (.).Restriction: You can specify only one decimal character in a number format model.
EEEE 9.9EEEE Returns a value using in scientific notation.
G 9G999 Returns in the specified position the group separator (the current value of the NLS_NUMERIC_CHARACTER parameter). You can specify multiple group separators in a number format model.Restriction: A group separator cannot appear to the right of a decimal character or period in a number format model.
L L999 Returns in the specified position the local currency symbol (the current value of the NLS_CURRENCY parameter).
MI 9999MI Returns negative value with a trailing minus sign (-).Returns positive value with a trailing blank.Restriction: The MI format element can appear only in the last position of a number format model.
PR 9999PR Returns negative value in <angle brackets>.Returns positive value with a leading and trailing blank.Restriction: The PR format element can appear only in the last position of a number format model.
RNrn RNrn Returns a value as Roman numerals in uppercase.Returns a value as Roman numerals in lowercase.Value can be an integer between 1 and 3999.
S S99999999S Returns negative value with a leading minus sign (-).Returns positive value with a leading plus sign (+).Returns negative value with a trailing minus sign (-).Returns positive value with a trailing plus sign (+).Restriction: The S format element can appear only in the first or last position of a number format model.
TM TM The text minimum number format model returns (in decimal output) the smallest number of characters possible. This element is case insensitive.The default is TM9, which returns the number in fixed notation unless the output exceeds 64 characters. If the output exceeds 64 characters, then Oracle Database automatically returns the number in scientific notation.Restrictions:

  • You cannot precede this element with any other element.
  • You can follow this element only with one 9 or one E (or e), but not with any combination of these. The following statement returns an error:SELECT TO_CHAR(1234, 'TM9e') FROM DUAL;
U U9999 Returns in the specified position the Euro (or other) dual currency symbol, determined by the current value of the NLS_DUAL_CURRENCY parameter.
V 999V99 Returns a value multiplied by 10n (and if necessary, round it up), where n is the number of 9’s after the V.
X XXXXxxxx Returns the hexadecimal value of the specified number of digits. If the specified number is not an integer, then Oracle Database rounds it to an integer.Restrictions:

  • This element accepts only positive values or 0. Negative values return an error.
  • You can precede this element only with 0 (which returns leading zeroes) or FM. Any other elements return an error. If you specify neither 0 nor FM with X, then the return always has one leading blank.
Datetime Format Element TO_* Datetime functions? Description
-
/
,
.
;
:
"text"
Yes Punctuation and quoted text is reproduced in the result.
AD
A.D.
Yes AD indicator with or without periods.
AM
A.M.
Yes Meridian indicator with or without periods.
BC
B.C.
Yes BC indicator with or without periods.
CC
SCC
Century.

  • If the last 2 digits of a 4-digit year are between 01 and 99 (inclusive), then the century is one greater than the first 2 digits of that year.
  • If the last 2 digits of a 4-digit year are 00, then the century is the same as the first 2 digits of that year.

For example, 2002 returns 21; 2000 returns 20.

D
Yes Day of week (1-7). This element depends on the NLS territory of the session.
DAY
Yes Name of day.
DD
Yes Day of month (1-31).
DDD
Yes Day of year (1-366).
DL
Yes Returns a value in the long date format, which is an extension of the Oracle Database DATE format, determined by the current value of the NLS_DATE_FORMAT parameter. Makes the appearance of the date components (day name, month number, and so forth) depend on the NLS_TERRITORY and NLS_LANGUAGE parameters. For example, in the AMERICAN_AMERICA locale, this is equivalent to specifying the format 'fmDay, Month dd, yyyy'. In the GERMAN_GERMANY locale, it is equivalent to specifying the format ‘fmDay, dd. Month yyyy‘.Restriction: You can specify this format only with the TS element, separated by white space.
DS
Yes Returns a value in the short date format. Makes the appearance of the date components (day name, month number, and so forth) depend on the NLS_TERRITORY and NLS_LANGUAGE parameters. For example, in the AMERICAN_AMERICA locale, this is equivalent to specifying the format ‘MM/DD/RRRR‘. In the ENGLISH_UNITED_KINGDOM locale, it is equivalent to specifying the format ‘DD/MM/RRRR‘.Restriction: You can specify this format only with the TS element, separated by white space.
DY
Yes Abbreviated name of day.
E
Yes Abbreviated era name (Japanese Imperial, ROC Official, and Thai Buddha calendars).
EE
Yes Full era name (Japanese Imperial, ROC Official, and Thai Buddha calendars).
FF [1..9]
Yes Fractional seconds; no radix character is printed. Use the X format element to add the radix character. Use the numbers 1 to 9 after FF to specify the number of digits in the fractional second portion of the datetime value returned. If you do not specify a digit, then Oracle Database uses the precision specified for the datetime data type or the data type’s default precision. Valid in timestamp and interval formats, but not in DATE formats.Examples: 'HH:MI:SS.FF'SELECT TO_CHAR(SYSTIMESTAMP, 'SS.FF3') from DUAL;
FM
Yes Returns a value with no leading or trailing blanks.See Also: Additional discussion on this format model modifier in the Oracle Database SQL Language Reference
FX
Yes Requires exact matching between the character data and the format model.See Also: Additional discussion on this format model modifier in the Oracle Database SQL Language Reference
HH
HH12
Yes Hour of day (1-12).
HH24
Yes Hour of day (0-23).
IW
Week of year (1-52 or 1-53) based on the ISO standard.
IYY
IY
I
Last 3, 2, or 1 digit(s) of ISO year.
IYYY
4-digit year based on the ISO standard.
J
Yes Julian day; the number of days since January 1, 4712 BC. Number specified with J must be integers.
MI
Yes Minute (0-59).
MM
Yes Month (01-12; January = 01).
MON
Yes Abbreviated name of month.
MONTH
Yes Name of month.
PM
P.M.
Yes Meridian indicator with or without periods.
Q
Quarter of year (1, 2, 3, 4; January – March = 1).
RM
Yes Roman numeral month (I-XII; January = I).
RR
Yes Lets you store 20th century dates in the 21st century using only two digits.See Also: “The RR Datetime Format Element”
RRRR
Yes Round year. Accepts either 4-digit or 2-digit input. If 2-digit, provides the same return as RR. If you do not want this functionality, then enter the 4-digit year.
SS
Yes Second (0-59).
SSSSS
Yes Seconds past midnight (0-86399).
TS
Yes Returns a value in the short time format. Makes the appearance of the time components (hour, minutes, and so forth) depend on the NLS_TERRITORY and NLS_LANGUAGE initialization parameters.Restriction: You can specify this format only with the DL or DS element, separated by white space.
TZD 
Yes Daylight saving information. The TZD value is an abbreviated time zone string with daylight saving information. It must correspond with the region specified in TZR. Valid in timestamp and interval formats, but not in DATE formats.Example: PST (for US/Pacific standard time); PDT (for US/Pacific daylight time).
TZH
Yes Time zone hour. (See TZM format element.) Valid in timestamp and interval formats, but not in DATE formats.Example: 'HH:MI:SS.FFTZH:TZM'.
TZM
Yes Time zone minute. (See TZH format element.) Valid in timestamp and interval formats, but not in DATE formats.Example: 'HH:MI:SS.FFTZH:TZM'.
TZR
Yes Time zone region information. The value must be one of the time zone region names supported in the database. Valid in timestamp and interval formats, but not in DATE formats.Example: US/Pacific
WW
Week of year (1-53) where week 1 starts on the first day of the year and continues to the seventh day of the year.
W
Week of month (1-5) where week 1 starts on the first day of the month and ends on the seventh.
X
Yes Local radix character.Example: 'HH:MI:SSXFF'.
Y,YYY
Yes Year with comma in this position.
YEAR
SYEAR
Year, spelled out; S prefixes BC dates with a minus sign (-).
YYYY
SYYYY
Yes 4-digit year; S prefixes BC dates with a minus sign.
YYY
YY
Y
Yes Last 3, 2, or 1 digit(s) of year.