Call (800) 766-1884 for Oracle support & training
Free Oracle Tips


Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance
 

Free Oracle Tips


 

HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

 

 

   
 

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:

Oracle Convert

Oracle Convert to number

 

  
 

Oracle performance tuning software 
 
 
 
 

Oracle performance tuning book

 

 
 
 
Oracle performance Tuning 10g reference poster
 
 
 
Oracle training in Linux commands
 
Oracle training Excel
 
Oracle training & performance tuning books
 

 

   

Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. 

Hit Counter