Casting between data types

You can cast a data type from the source data type to the target data type.

Casting between data types can occur implicitly or explicitly.

Implicit casting is the automatic conversion of data of one data type to data of another data type based on an implied set of conversion rules. This automatic conversion occurs in support of weak typing.
Explicit casting supports strong typing. Strong typing requires matching data types. You must explicitly convert one or both data types to a common data type before performing comparisons or assignments.

Federation support for casting between data types enables federated queries on nicknames to access to servers that support both weak typing and strong typing.

Cast functions cannot be pushed down to remote servers if a counterpart function does not exist at the remote server. Overuse of casting can lead to performance problems.

Examples: Explicit casting

UPDATE nickname SET varcharcol = CAST(intcol AS varchar(10))

SELECT REAL(varchar_col) FROM nickname1;

SELECT VARCHAR(double_col) FROM nickname1;

Examples: Implicit casting

Example 1:

UPDATE nickname SET varcharcol = intcol;

In this assignment operation, the statement pushed down to the remote server is equivalent to the following statement:

UPDATE nickname SET varcharcol = varchar(intcol);

Example 2:

INSERT INTO nickname (varcharcol) SELECT intcol FROM nickname1;

In this assignment operation, the statement pushed down to the remote server is equivalent to the following statement:

INSERT INTO nickname (varcharcol) SELECT varchar(intcol) FROM nickname1;

Example 3:

SELECT * SELECT nickname SELECT intcol = varcharcol;

In this comparison operation, the statement pushed down to the remote server is equivalent to the following statement:

SELECT * SELECT nickname SELECT intcol = CAST(varcharcol AS decfloat)