Choosing the Kind of User-Defined Cast

You specify how a database server treats a cast when you use the CREATE CAST statement. The database server supports two kinds of user-defined casts:

• Implicit cast
• Explicit cast

  The database server invokes an explicit cast to perform conversions between two data types only when you specify the CAST AS keywords or the double colon (::) cast operator.

Implicit Cast

An implicit cast governs what automatic data conversion occurs for an operation that involves two different data types. All casts between built-in data types are implicit.

The database server automatically invokes an implicit cast when it performs the following tasks:

• It passes arguments of one data type to a UDR whose parameters are of another data type.
• It evaluates expressions and needs to operate on two similar data types.

Conversion of one data type to another can involve loss of data. Be careful of creating implicit casts for such conversions. The end user cannot control when the database server invokes an implicit cast and therefore cannot avoid the loss of data that is inherent to such a conversion.

The database server invokes an implicit cast automatically, without a cast operator. However, you also can explicitly invoke an implicit cast with the CAST AS keywords or the :: cast operator.

To create an implicit cast, specify the IMPLICIT keyword of the CREATE CAST statement. The following CREATE CAST statement creates an implicit cast from the percent data type to the DECIMAL data type:

CREATE IMPLICIT CAST (percent AS DECIMAL)

Explicit Cast

An explicit cast governs what data conversion an end user can specify for UDTs (such as opaque data types, distinct data types, and row types). The database server invokes an explicit cast only when it encounters one of the following syntax structures:

• The CAST AS keywords

  For example, the following expression uses the CAST AS keywords to invoke an explicit cast between the percent and INTEGER data types:

  WHERE col1 > (CAST percent AS INTEGER)

• The :: cast operator

  For example, the following expression uses the cast operator to invoke an explicit cast between the percent and INTEGER data types:

  WHERE col1 > (percent::INTEGER)

The conversion of one data type to another can involve loss of data. If you define such conversions as explicit casts, the end user can control when the loss of data that is inherent to such a conversion is acceptable.

To create an explicit cast, specify the EXPLICIT keyword of the CREATE CAST statement. If you omit the keyword, the default is an explicit cast. Both of the following CREATE CAST statements create explicit casts from the percent data type to the INTEGER data type:

CREATE EXPLICIT CAST (percent AS INTEGER)
CREATE CAST (percent AS INTEGER)