Syntax  

CHARTOROWID(char)

Purpose  

Converts a value from CHAR or VARCHAR2 datatype to ROWID datatype.

Example  

SELECT ename FROM emp
   WHERE ROWID = CHARTOROWID('AAAAfZAABAAACp8AAO');
 
ENAME
----------
LEWIS

Syntax  

CONVERT(char, dest_char_set [,source_char_set] )

Purpose  

Converts a character string from one character set to another.

The char argument is the value to be converted.

The dest_char_set argument is the name of the character set to which char is converted.

The source_char_set argument is the name of the character set in which char is stored in the database. The default value is the database character set.

Both the destination and source character set arguments can be either literals or columns containing the name of the character set.

For complete correspondence in character conversion, it is essential that the destination character set contains a representation of all the characters defined in the source character set. Where a character does not exist in the destination character set, a replacement character appears. Replacement characters can be defined as part of a character set definition.

Example  

SELECT CONVERT('Groß', 'US7ASCII', 'WE8HP') 
"Conversion"
  FROM DUAL;

Conversion
----------
Gross

Common character sets include:

US7ASCII

WE8DEC

WE8HP

F7DEC

WE8EBCDIC500

WE8PC850

WE8ISO8859P1

US 7-bit ASCII character set

DEC West European 8-bit character set

HP West European Laserjet 8-bit character set

DEC French 7-bit character set

IBM West European EBCDIC Code Page 500

IBM PC Code Page 850

ISO 8859-1 West European 8-bit character set  

Syntax  

HEXTORAW(char)

Purpose  

Converts char containing hexadecimal digits to a raw value.

Example  

INSERT INTO graphics (raw_column)
  SELECT HEXTORAW('7D') FROM DUAL;

Syntax  

RAWTOHEX(raw)

Purpose  

Converts raw to a character value containing its hexadecimal equivalent.

Example  

SELECT RAWTOHEX(raw_column) "Graphics"
   FROM graphics;

Graphics
--------
7D  

Syntax  

ROWIDTOCHAR(rowid)

Purpose  

Converts a ROWID value to VARCHAR2 datatype. The result of this conversion is always 18 characters long.

Example  

SELECT ROWID 
     FROM offices
     WHERE 
     ROWIDTOCHAR(ROWID) LIKE '%Br1AAB%';

ROWID
------------------
AAAAZ6AABAAABr1AAB

Syntax  

TO_CHAR(d [, fmt [, 'nlsparams'] ])

Purpose  

Converts d of DATE datatype to a value of VARCHAR2 datatype in the format specified by the date format fmt. If you omit fmt, d is converted to a VARCHAR2 value in the default date format. For information on date formats, see "Format Models".

The 'nlsparams' specifies the language in which month and day names and abbreviations are returned. This argument can have this form:

'NLS_DATE_LANGUAGE = language'

If you omit nlsparams, this function uses the default date language for your session.

Example  

SELECT TO_CHAR(HIREDATE, 'Month DD, YYYY')
    "New date format" FROM emp
    WHERE ename = 'BLAKE';
 
New date format
------------------
May       01, 1981

Syntax  

TO_CHAR(n [, fmt [, 'nlsparams'] ])

Purpose

Converts n of NUMBER datatype to a value of VARCHAR2 datatype, using the optional number format fmt. If you omit fmt, n is converted to a VARCHAR2 value exactly long enough to hold its significant digits. For information on number formats, see "Format Models".

The 'nlsparams' specifies these characters that are returned by number format elements:

- decimal character

- group separator

- local currency symbol

- international currency symbol

This argument can have this form:

'NLS_NUMERIC_CHARACTERS = ''dg''
NLS_CURRENCY = ''text''
NLS_ISO_CURRENCY = territory '

The characters d and g represent the decimal character and group separator, respectively. They must be different single-byte characters. Note that within the quoted string, you must use two single quotation marks around the parameter values. Ten characters are available for the currency symbol.

If you omit 'nlsparams' or any one of the parameters, this function uses the default parameter values for your session.

Example 1  

In this example, the output is blank padded to the left of the currency symbol.

SELECT TO_CHAR(-10000,'L99G999D99MI') "Amount"
     FROM DUAL;

Amount
--------------
  $10,000.00-

Example 2

SELECT TO_CHAR(-10000,'L99G999D99MI',
'NLS_NUMERIC_CHARACTERS = '',.''
NLS_CURRENCY = ''AusDollars'' ') "Amount"
     FROM DUAL;

Amount
-------------------
AusDollars10.000,00-

Notes:

• In the optional number format fmt, L designates local currency symbol and MI designates a trailing minus sign. See Table 3-13 for a complete listing of number format elements.

• During a conversion of Oracle numbers to string, if a rounding operation occurs that overflows or underflows the Oracle NUMBER range, then ~ or -~ may be returned, representing infinity and negative infinity, respectively. This event typically occurs when you are using TO_CHAR() with a restrictive number format string, causing a rounding operation.

Syntax  

TO_DATE(char [, fmt [, 'nlsparams'] ])

Purpose  

Converts char of CHAR or VARCHAR2 datatype to a value of DATE datatype. The fmt is a date format specifying the format of char. If you omit fmt, char must be in the default date format. If fmt is 'J', for Julian, then char must be an integer. For information on date formats, see "Format Models".

The 'nlsparams' has the same purpose in this function as in the TO_CHAR function for date conversion.

Do not use the TO_DATE function with a DATE value for the char argument. The returned DATE value can have a different century value than the original char, depending on fmt or the default date format.

For information on date formats, see "Date Format Models".

Example  

INSERT INTO bonus (bonus_date)
  SELECT TO_DATE(
    'January 15, 1989, 11:00 A.M.',
    'Month dd, YYYY, HH:MI A.M.',
     'NLS_DATE_LANGUAGE = American')
     FROM DUAL;

Syntax  

TO_MULTI_BYTE(char)

Purpose  

Returns char with all of its single-byte characters converted to their corresponding multibyte characters. Any single-byte characters in char that have no multibyte equivalents appear in the output string as single-byte characters. This function is only useful if your database character set contains both single-byte and multibyte characters.

Syntax  

TO_NUMBER(char [,fmt [, 'nlsparams'] ])

Purpose  

Converts char, a value of CHAR or VARCHAR2 datatype containing a number in the format specified by the optional format model fmt, to a value of NUMBER datatype.

Example 1  

UPDATE emp SET sal = sal + 
   TO_NUMBER('100.00', '9G999D99')
  WHERE ename = 'BLAKE';

The 'nlsparams' string in this function has the same purpose as it does in the TO_CHAR function for number conversions.

Example 2  

SELECT TO_NUMBER('-AusDollars100','L9G999D99',
   ' NLS_NUMERIC_CHARACTERS = '',.''
     NLS_CURRENCY            = ''AusDollars''
   ') "Amount"
     FROM DUAL;

    Amount
----------
      -100

Syntax  

TO_SINGLE_BYTE(char)

Purpose  

Returns char with all of its multibyte character converted to their corresponding single-byte characters. Any multibyte characters in char that have no single-byte equivalents appear in the output as multibyte characters. This function is only useful if your database character set contains both single-byte and multibyte characters.