Oracle provides a comprehensive set of conversion functions and operators to be used in SQL and PL/SQL; a complete list is shown in Table 7-1. Most of these functions are described in other chapters (for those, the table indicates the chapter number). For functions not described elsewhere, brief descriptions are provided later in this chapter.
| Table 7-1. The built-in conversion functions | ||
| Name | Description | Chapter |
| ASCIISTR | Converts a string in any character set to an ASCII string in the database character set. | |
| CAST | Converts one built-in datatype or collection-typed value into another built-in datatype or collection-typed value; this very powerful conversion mechanism can be used as a substitute for traditional functions like TO_DATE. | 7, 9, 10 |
| CHARTOROWID | Converts a string to a ROWID. | |
| CONVERT | Converts a string from one character set to another. | |
| FROM_TZ | Adds time zone information to a TIMESTAMP value, thus converting it into a TIMESTAMP WITH TIME ZONE value. | |
| HEXTORAW | Converts from hexadecimal to raw format. | |
| MULTISET | Maps a database table to a collection. | |
| NUMTODSINTERVAL | Converts a number (or numeric expression) to an INTERVAL DAY TO SECOND literal. | |
| NUMTOYMINTERVAL | Converts a number (or numeric expression) to an INTERVAL YEAR TO MONTH literal. | |
| RAWTOHEX, RAWTONHEX | Converts from raw value to hexadecimal. | |
| REFTOHEX | Converts a REF value into a character string containing the hexadecimal representation of the REF value. | |
| ROWIDTOCHAR, ROWIDTONCHAR | Converts a binary ROWID value to a character string. | |
| TABLE | Maps a collection to a database table; this is the inverse of MULTISET. | |
| THE | Maps a single column value in a single row into a virtual database table. | |
| TO_CHAR, TO_ NCHAR (number version) | Converts a number to a string (VARCHAR2 or NVARCHAR2, respectively). | |
| TO_CHAR, TO_NCHAR (date version) | Converts a date to a string. | |
| TO_CHAR, TO_NCHAR (character version) | Converts character data between the database character set and the national character set. | |
| TO_BLOB | Converts from a RAW value to a BLOB. | |
| TO_CLOB, TO_NCLOB | Converts from a VARCHAR2, NVARCHAR2, or NCLOB value to a CLOB (or NCLOB). | |
| TO_DATE | Converts a string to a date. | |
| TO_DSINTERVAL | Converts a character string of a CHAR, VARCHAR2, NCHAR, or NVARCHAR2 datatype to an INTERVAL DAY TO SECOND type. | |
| TO_LOB | Converts from a LONG to a LOB. | |
| TO_MULTI_BYTE | Where possible, converts single-byte characters in the input string to their multibyte equivalents. | |
| TO_NUMBER | Converts a string to a number. | |
| TO_RAW | Converts from a BLOB to a RAW. | |
| TO_SINGLE_BYTE | Converts multibyte characters in the input string to their corresponding single-byte characters. | |
| TO_TIMESTAMP | Converts a character string to a value of type TIMESTAMP. | |
| TO_TIMESTAMP_TZ | Converts a character string to a value of type TO_TIMESTAMP_TZ. | |
| TO_TIMESTAMP_LTZ | Converts a character string to a value of type TO_TIMESTAMP_LTZ. | |
| TO_YMINTERVAL | Converts a character string of a CHAR, VARCHAR2, NCHAR, or NVARCHAR2 datatype to an INTERVAL YEAR TO MONTH type. | |
| TRANSLATE... USING | Converts supplied text into the character set specified for conversions between the database character set and the national character set. | |
| UNISTR | Takes as its argument a string in any character set and returns it in Unicode in the database Unicode character set. |
