SQL Reference

CAST Specifications

 
cast-specification
 
|--CAST--(--+-expression-------+--AS--data-type----------------->
            +-NULL-------------+
            '-parameter-marker-'
 
>----+-----------------------------------+---)------------------|
     |       (1)                         |
     '-SCOPE-------+-typed-table-name-+--'
                   '-typed-view-name--'

Notes:

The SCOPE clause only applies to the REF data type.

The CAST specification returns the cast operand (the first operand) cast to the type specified by the data type.

expression

If the cast operand is an expression (other than parameter marker or NULL), the result is the argument value converted to the specified target data type.

The supported casts are shown in Table 6 where the first column represents the data type of the cast operand (source data type) and the data types across the top represent the target data type of the CAST specification. If the cast is not supported an error will occur (SQLSTATE 42846).

When casting character strings (other than CLOBs) to a character string with a different length, a warning (SQLSTATE 01004) is returned if truncation of other than trailing blanks occurs. When casting graphic character strings (other than DBCLOBs) to a graphic character string with a different length, a warning (SQLSTATE 01004) is returned if truncation of other than trailing blanks occurs. For BLOB, CLOB and DBCLOB cast operands, the warning is issued if any characters are truncated.

NULL

If the cast operand is the keyword NULL, the result is a null value that has the specified data type.

parameter-marker

A parameter marker (specified as a question mark character) is normally considered an expression, but is documented separately in this case because it has a special meaning. If the cast operand is a parameter-marker, the specified data type is considered a promise that the replacement will be assignable to the specified data type (using store assignment for strings). Such a parameter marker is considered a typed parameter marker. Typed parameter markers will be treated like any other typed value for the purpose of function resolution, DESCRIBE of a select list or for column assignment.

data type

The name of an existing data type. If the type name is not qualified, the SQL path is used to do data type resolution. A data type that has an associated attributes like length or precision and scale should include these attributes when specifying data type (CHAR defaults to a length of 1 and DECIMAL defaults to a precision of 5 and scale of 0 if not specified). Restrictions on the supported data types are based on the specified cast operand.

For a cast operand that is an expression, see Casting Between Data Types for the target data types that are supported based on the data type of the cast operand (source data type).
For a cast operand that is the keyword NULL, any existing data type can be used.
For a cast operand that is a parameter marker, the target data type can be any existing data type. If the data type is a user-defined distinct type, the application using the parameter marker will use the source data type of the user-defined distinct type. If the data type is a user-defined structured type, the application using the parameter marker will use the input parameter type of the TO SQL transform function for the user-defined structured type.

SCOPE

When the data type is a reference type, a scope may be defined that identifies the target table or target view of the reference.

typed-table-name: The name of a typed table. The table must already exist (SQLSTATE 42704). The cast must be to data-type REF(S), where S is the type of typed-table-name (SQLSTATE 428DM).
typed-view-name: The name of a typed view. The view must exist or have the same name as the view being created that includes the cast as part of the view definition (SQLSTATE 42704). The cast must be to data-type REF(S), where S is the type of typed-view-name (SQLSTATE 428DM).

When numeric data is cast to character the result data type is a fixed-length character string (see CHAR). When character data is cast to numeric, the result data type depends on the type of number specified. For example, if cast to integer, it would become a large integer (see INTEGER).

Examples:

An application is only interested in the integer portion of the SALARY (defined as decimal(9,2)) from the EMPLOYEE table. The following query, including the employee number and the integer value of SALARY, could be prepared.
```
  SELECT EMPNO, CAST(SALARY AS INTEGER) FROM EMPLOYEE
```
Assume the existence of a distinct type called T_AGE that is defined on SMALLINT and used to create column AGE in PERSONNEL table. Also assume the existence of a distinct type called R_YEAR that is defined on INTEGER and used to create column RETIRE_YEAR in PERSONNEL table. The following update statement could be prepared.
```
  UPDATE PERSONNEL SET RETIRE_YEAR =?
                   WHERE AGE = CAST( ? AS T_AGE)
```
The first parameter is an untyped parameter marker that would have a data type of R_YEAR, although the application will use an integer for this parameter marker. This does not require the explicit CAST specification because it is an assignment.
The second parameter marker is a typed parameter marker that is cast as a distinct type T_AGE. This satisfies the requirement that the comparison must be performed with compatible data types. The application will use the source data type (which is SMALLINT) for processing this parameter marker.
Successful processing of this statement assumes that the function path includes the schema name of the schema (or schemas) where the two distinct types are defined.

[ Top of Page | Previous Page | Next Page ]