Oracle provides conversion functions that will easily convert values
of one datatype to another. While Oracle will perform implicit
conversions of datatypes for you, this can lead to performance problems
in your applications and to compatibility problems in future versions of
Oracle.
Also see the Oracle convert
function.
The following Oracle conversion functions can be used to convert
Oracle data:
to_char()
The to_char() Oracle conversion function is probably the most
commonly used conversion function. The conversion function converts both
numerical data and date data to datatype varchar2.
The simplest way of using to_char() to convert Oracle date
information is as follows:
to_char ('02/14/97')
This converts the Oracle date into a character string in the
default date format (numerical month, numerical day, and a two-digit
year, separated by slashes). However, the most common use of
to_char() is to convert Oracle dates to type varchar2 in a
specific format, as shown in this example,
to_char ('14-FEB-97', 'DD-MON-YYYY')
which returns the following string:
14-FEB-1997
Using to_char() to convert numerical data is very similar to
using the function with dates. One common use of this Oracle
conversion function is to simply convert data from a numerical
datatype to type varchar2. For example,
to_char (25000)
returns
25000
When converting numerical Oracle data, to_char() can also
take a second argument; a format model for the output of the function.
For example,
to_char (25000, '$99,999.99')
returns
$25,000.00
Chapter 3 lists the elements that can be used in format models for
numerical and date data.
to_date()
The to_date() Oracle conversion function is used to convert
character data to the date datatype. Like to_char(), this
Oracle conversion function can be called with a single parameter, much
like
to_date ('02-MAY-97')
which returns a value of type date. to_date() may
also be called with a second parameter, which instructs the function
to convert the specified string from the specified format into a
standard date. For example,
to_date ('02 May 1997', 'DD MONTH YYYY')
returns
02-MAY-97
Valid elements of the format model are discussed in Chapter 3. The
examples provided here have all used the default Oracle date format.
to_number()
The to_number() Oracle conversion function converts data of
type char or varchar2 to type number. This Oracle
conversoin function can accept a single parameter. For example,
to_number ('25000')
returns
25000
The Oracle conversion function can also accept a format model as
the second parameter, like the to_char() and to_date()
functions. For example,
to_number ('25000', '$99,999.99')
returns
$25,000.00
Valid elements of the format model are discussed in Chapter 3.
This is an
excerpt from the book "High Performance Oracle Database Automation"
by Jonathan Ingram and Donald K. Burleson, Series Editor.
For more info on Oracle convert See these related links:
Oracle Convert Date to
Number
Oracle Convert to Number
Oracle Conversion Functions - to_char() to_date()
to_number() function
Oracle 10g Convert
Clause Restrictions Usage Notes
