Conversion Functions

Conversion Functions

Conversion functions convert a value from one datatype to another. Generally, the form of the function names follows the convention datatype TO datatype. The first datatype is the input datatype. The second datatype is the output datatype. The SQL conversion functions are:

ASCIISTR BIN_TO_NUM CAST CHARTOROWID COMPOSE CONVERT DECOMPOSE HEXTORAW NUMTODSINTERVAL NUMTOYMINTERVAL RAWTOHEX

RAWTONHEX ROWIDTOCHAR ROWIDTONCHAR TO_CHAR (character) TO_CHAR (datetime) TO_CHAR (number) TO_CLOB TO_DATE TO_DSINTERVAL TO_LOB TO_MULTI_BYTE

TO_NCHAR (character) TO_NCHAR (datetime) TO_NCHAR (number) TO_NCLOB TO_NUMBER TO_SINGLE_BYTE TO_YMINTERVAL TRANSLATE ... USING UNISTR

1

Conversion Functions

Datatype conversion 1. Implicit datatype conversion 2. Explicit datatype conversion

Conversion Functions in addition to Oracle datatypes, columns of tables in an Oracle8 database can be defined usiii? ANSI. DB2, and SQL/DS datattypes. However, ths Oracle Server internally converts such datatypes to OracIe8 datatypes. In some cases, Oracle Server allows data of one datatype where it expects data of a different datatype. This is allowed when Oracle Server can automatically converts the data to the expected datatype. This datatype conversion can be done iimplicitly by Oracle Server or explicitly by the user. Implicit datatvpe conversions work according to the rules explained in next two slides. Explicit datatype conversions are done by using the conversion functions. Conversion functions convert a value from one datatype to another. Generally, the form of the function names follows the convention datatype TO datatype. The first datatype is the input dataty; the last datatype is the output.

2

Implicit Datatype Conversion

For assignments, the Oracle can automatically convert the follovving:

From

To

VARCHAR2 or CHAR NUMBER

VARCHAR2 or CHAR DATE

NUMBER

VARCHAR2

DATE

VARCHAR2

Implicit Datatype Conversion

The assignment succeeds if the Oracle Server can convert the dalatype of the value used in the assignment to that of the assignment target.

Implicit Datatype Conversion

For expression evaluation, the Oracle Server can automatically convert the follovving:

From

To

VARCHAR2 or CHAR NUMBER

VARCHAR2 or CHAR DATE

Implicit Datatype Conversion

In general, the Oracle Server uses the rule for expression when a datatype conversion is needed in places not covered by a rule for assignment conversions.

Note: CHAR to NUMBER conversions succeed only if the character string represents a valid number. CHAR to DATE conversions succeed only if the character string has the default format DD-MON-YY.

Explicit Datatype Conversion

Three Main Functions

SQL provides three functions to convert a value from one datatype to another:

TO_CHAR (number | date [ , `fmt'] )

Converts a number or a date value to a VARCHAR2 character string with format model fmt.

TO_NUMBER (char [ , `fmt' ] )

Converts a character string containing digits to a number with the optional format model fmt.

TO_DATE (char [ , `fmt' ] )

Converts a character string representing a date to a date value according to the fmt specified (If fmt is omitted, format is DD-MONYY. )

 ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download