 |
|
Oracle Conversion
Functions
Oracle Tips by
Burleson
|
Oracle provides conversion functions so values of one datatype can
be easily converted to another datatype. While Oracle will perform
implicit conversions of datatypes for you, this could lead to
performance problems in your applications and to compatibility
problems in future versions of Oracle. I strongly recommend that you
use these conversion functions instead of relying on Oracle’s implicit
conversions.
to_char()
The to_char() function is probably the most commonly used
conversion function. The function converts both numerical data and
date data to datatype varchar2.
The simplest way of using to_char() to convert date
information is as follows:
to_char ('02/14/97')
This converts the 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 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() with numerical data is very similar to using
the function with dates. One common use is to simply convert data from
a numerical datatype to type varchar2. For example,
to_char (25000)
returns
25000
When converting numerical 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() function is used to convert character data to
the date datatype. Like to_char(), this 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() function converts data of type char
or varchar2 to type number. The function can accept a
single parameter. For example,
to_number ('25000')
returns
25000
The 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 information
on Oracle conversion functions, please visit the following links:
|
