14. Conversion Functions

Contents:
Conversion Formats
Conversion Function Descriptions
Conversion Function Examples

Whenever PL/SQL performs an operation involving one or more values, it must first convert the data to be the right datatype for the operation. As Chapter 4, Variables and Program Data , describes, there are two kinds of conversion: implicit and explicit. An implicit conversion is performed by PL/SQL as needed to execute a statement. An explicit conversion takes place when you use a built-in conversion function to force the conversion of a value from one datatype to another. This chapter describes each of these functions.

If you do not use a conversion function to explicitly convert your data, or if you do use those functions and additional conversion is still needed, PL/SQL attempts to convert the data implicitly to the datatypes needed to perform the operation. I recommend that you avoid allowing either the SQL or PL/SQL languages to perform implicit conversions on your behalf. Whenever possible, use a conversion function to guarantee that the right kind of conversion takes place. Implicit conversion has a number of drawbacks, summarized in Section 4.2.8.2, "Implicit data conversions" in Chapter 4 .

Explicit conversions help you to avoid unpleasant surprises, maximize performance, and make your code more self-documenting. When you perform an explicit conversion involving dates or numbers (using TO_CHAR, TO_DATE, or TO_NUMBER), you can specify a conversion format mask. PL/SQL uses this mask to either interpret the input value or format the output value.

Table 14.1 summarizes the PL/SQL conversion functions described in this chapter.

Table 14.1: The Built-In Conversion Functions
Name	Description
CHARTOROWID	Converts a string to a ROWID.
CONVERT	Converts a string from one character set to another.
HEXTORAW	Converts from hexadecimal to raw format.
RAWTOHEX	Converts from raw value to hexadecimal.
ROWIDTOCHAR	Converts a binary ROWID value to a character string.
TO_CHAR	Converts a number or date to a string.
TO_DATE	Converts a string to a date.
TO_NUMBER	Converts a string to a number.

14.1 Conversion Formats

Several of the conversion functions (TO_CHAR, TO_DATE, and TO_NUMBER) use format models to determine the format of the converted data. Format models convert between strings and dates, and strings and numbers. This section discusses these format models, which are then put to use in the function descriptions.

14.1.1 Date Format Models

In Versions 6 and earlier of the Oracle RDBMS, the default format for dates as character values was DD-MON-YY, a cause of consternation for many developers and users. While this format is common in many parts of the world, very few people use it in the United States.

With Oracle7 you can specify your own default date format (which takes effect on the initialization or startup of the RDBMS instance) with the NLS_DATE_FORMAT[ 1 ] parameter as follows:

[1] NLS is an abbreviation for National Language Support.

NLS_DATE_FORMAT = `MM/DD/YYYY'

The default date format is also set implicitly with another initialization parameter, NLS_TERRITORY. When you specify an NLS_TERRITORY value, you set conventions for date format, date language, numeric formats, currency symbols, and week start day.

Even with this flexibility, the database still supports only a single default date format in a given instance. Both developers and users must be aware of this format when working with dates. Later sections of this chapter explore approaches in PL/SQL that give the user much more flexibility when entering dates in their applications.

As you can see, format masks (such as MMDDYY and Month DD, YYYY) play an important role in the conversion of date and character data. Table 14.2 provides the full set of date format masks and explains how to use them in all their variations. You can use the format elements in any combination, in any order. You can even use the same format element more than once in your format mask. Following the table are examples showing these variations.

Table 14.2: Date Format Model Elements
Mask	Description
SCC or CC	The century. If the SCC format is used, any B.C. dates are prefaced with a hyphen (`-` ).
SYYYY or YYYY	The four-digit year. If the SYYYY format is used, any B.C. dates are prefaced with a hyphen (`-` ).
IYYY	The four-digit ISO standard year.
YYY or YY or Y	The last three, two, or one digits of the year. The current century is the default.
IYY or IY or I	The last three, two, or one digits of the ISO standard year. The current century is the default.
Y,YYY	The four-digit year with a comma.
SYEAR or YEAR or SYear or Year	The year spelled out. The S prefix places a negative sign in front of B.C. dates.
RR	The last two digits of the year. This format is used to display years in centuries other than our own. See Section 14.3.3, "RR: Changing Millenia" .
BC or AD	The B.C. or A.D. indicator, without periods.
B.C. or A.D.	The B.C. or A.D. indicator, with periods.
Q	The quarter of the year, from 1 through 4. January through March are in the first quarter, April through June in second quarter, etc.
MM	The number of the month in the year, from 01 through 12. January is month number 01, September is 09, etc.
RM	The Roman numeral representation of the month number, from I through XII. January is I, September is IX, etc.
MONTH or Month	The name of the month, either in upper- or mixed-case format.
MON or Mon	The abbreviated name of the month, as in JAN for January.
WW	The week in the year, from 1 through 53.
IW	The week in the year, from 1 through 52 or 1 through 53, based on the ISO standard.
W	The week in the month, from 1 through 5. Week 1 starts on the first day of the month and ends on the seventh.
DDD	The day in the year, from 1 through 366.
DD	The day in the month, from 1 through 31.
D	The day in the week, from 1 through 7. The day of the week that is decreed the first day is specified implicitly by the NLS_TERRITORY initialization parameter for the database instance.
DAY or Day	The name of the day in upper- or mixed-case format.
DY	The abbreviated name of the day, as in TUE for Tuesday.
J	The Julian day format of the date (counted as the number of days since January 1, 4712 B.C., the earliest date supported by the Oracle RDBMS).
AM or PM	The meridian indicator (morning or evening) without periods.
A.M. or P.M.	The meridian indicator (morning or evening) with periods.
HH or HH12	The hour in the day, from 1 through 12.
HH24	The hour in the day, from 0 through 23.
MI	The minutes component of the date's time, from 0 through 59.
SS	The seconds component of the date's time, from 0 through 59.
SSSSS	The number of seconds since midnight of the time component. Values range from 1 through 86399, with each hour comprising 3600 seconds.
TH	Suffix which converts a number to its ordinal format; for example, 4 becomes 4th and 1 becomes 1st. This element can appear only at the end of the entire format mask. The return value is always in English, regardless of the date language.
SP	Suffix which converts a number to its spelled format; for example, 4 becomes FOUR, 1 becomes ONE, and 221 becomes TWO HUNDRED TWENTY-ONE. This element can appear only at the end of the entire format mask. The return value is always in English, regardless of the date language.
SPTH	Suffix which converts a number to its spelled and ordinal format; for example, 4 becomes FOURTH and 1 becomes FIRST. This element can appear only at the end of the entire format mask. The return value is always in English, regardless of the date language.
FX	Element which requires exact pattern matching between data and format model. (FX stands for Format eXact.)
FM	Element which toggles suppression of blanks in output from conversion. (FM stands for Fill Mode.)
Other text	Any punctuation, such as a comma (`,` ) or slash (`/` ) or hyphen (`-` ), will be reproduced in the formatted output of the conversion. You can also include text within double quotes (`"` ) and this text will then be represented as entered in the converted value. See examples in TO_CHAR for an illustration of this element.

Note that whenever a date format returns a spelled value (words rather than numbers, as with MONTH, MON, DAY, DY, AM, and PM), the language used to spell these words is determined by the National Language Support parameters, NLS_DATE_LANGUAGE and NLS_LANGUAGE, or by the optional date language argument you can pass to both TO_CHAR and TO_DATE.

Here are some examples of date format masks composed of the above format elements:

'Month DD, YYYY'
'MM/DD/YY Day A.M.'
'Year Month Day HH24:MI:SS'
'J'
'SSSSS-YYYY-MM-DD'
'"A beautiful summer morning on the" DDth" day of "Month'

See the description of the TO_CHAR and TO_DATE functions for more examples of the use and resulting values of these masks.

14.1.2 Number Format Models

The number formats are used in both TO_CHAR and TO_NUMBER. The number format in TO_CHAR translates a numeric value to a VARCHAR2 datatype. The number format in TO_NUMBER translates a VARCHAR2 value to a numeric datatype.

A number format mask can comprise one or more elements from Table 14.3 . The resulting format of the character string (or the converted numeric value) will reflect the combination of the format elements. You will find examples of different applications of the format models in the descriptions of both the TO_CHAR and TO_NUMBER functions.

Format elements with a description starting with "Prefix:" can be used only at the beginning of the complete format mask. Format elements with a description starting with "Suffix:" can be used only at the end of the complete format mask.

Table 14.3: Number Format Model Elements
Format Elements	Description
9	Each 9 represents a significant digit to be returned. Leading zeros in a number are displayed or treated as blanks.
0	Each represents a significant digit to be returned. Leading zeros in a number are displayed or treated as zeros.
$	Prefix: puts a dollar sign in front of the number.
B	Prefix: returns a zero value as blanks, even if the format element was used to show a leading zero.
MI	Suffix: places a minus sign (`-` ) after the number if it is negative. For positive values it returns a trailing space, which is different from NULL.
S	Prefix: places a plus sign (`+` ) in front of a positive number and a minus sign (`-` ) before a negative number.
PR	Suffix: places angle brackets (`<` and `>` ) around a negative value. For positive values it places leading and trailing spaces around the number.
D	Specifies the location of the decimal point in the returned value. All format elements to the left of the D will format the integer component of the value. All format elements to the right of the D will format the fractional part of the value. The character used for the decimal character is determined by the database initialization parameter NLS_NUMERIC_CHARACTERS.
G	Specifies the location of the group separator (for example, a comma to separate thousands as in 6,734) in the returned value. The character used for the group separator is determined by the database initialization parameter NLS_NUMERIC_CHARACTERS.
C	Specifies the location of the ISO currency symbol in the returned value.
L	Specifies the location of the local currency symbol (such as $) in the returned value.
, (comma)	Specifies that a comma be returned in that location in the return value.
. (period)	Specifies that a period be returned in that location in the return value.
V	Multiplies the number to the left of the V in the format model by 10 raised to the n th power, where n is the number of 9s found after the V in the format model.
EEEE	Suffix: specifies that the value be returned in scientific notation.
RN or rn	Specifies that the return value be converted to upper- or lowercase Roman numerals. The range of valid numbers for conversion to Roman numerals is between 1 and 3999. The value must be an integer.

Here are some examples of numeric format masks built from these elements:

9.999EEEE
00V99
S9,999,999
00009MI
999D99
9G999G999
L999.99