Database Reference
In-Depth Information
9.2.4
Datatype Conversion Functions
Before we get to examples and combining of functions, where we often use
conversion functions, we will describe the most useful datatype conversion
functions. Conversion functions are important in two ways:
1. The obvious is that they allow conversions between different
datatypes.
2. The more important and less obvious is that conversion functions
allow the combination of different datatypes into expressions to
produce a single datatype result.
The conversion functions we are most interested in are as listed here.
Date conversion functions:
TO_DATE(date[, format])
. Converts a string representation of
a date to a date.
TO_CHAR(date [, format])
. Converts a date to a string.
Number conversion functions:
TO_CHAR(number [, format])
. Converts a number to a string.
TO_NUMBER(string [, format])
. Converts a string to a number.
Other conversion functions:
TO_CLOB(string)
.
Converts a simple string to a CLOB or
binary text object. Converting from a CLOB object to a string is
transparent. Transparent is a computerese jargon term meaning
automatic. No conversion function is required.
TO_N{CHAR|DATE|NUMBER|CLOB}(expression [, format])
.
This function can be used to convert an expression, string, date,
or number from the database character set to the national charac-
ter set. For example, TO_NCHAR converts an incoming value to
a national character set value.
Now we need to examine conversion function format options. Conver-
sion formats are applied as a datatype conversion takes place.
9.2.4.1
Number Conversion Function Formats
TO_CHAR(number[, format]) is used to convert from a number to a
string, and TO_NUMBER(string [, format]) is used to convert from a
string to a number. As with the ROUND and TRUNC functions, there are
numerous applicable formatting rules.
