If you do not indicate a length for character string types, an appropriate length is chosen. If neither precision nor scale
is specified for a DECIMAL conversion, a default value is selected. It is recommended that you explicitly indicate the precision
and scale in your CAST function.
The ability to convert depends on the value used in the conversion. The values in the original data type must be compatible
with the new data type to avoid generating a conversion error.
Use the following chart to determine whether a conversion is supported:
To convert between a VARBINARY and a UNIQUEIDENTIFIER, the VARBINARY value must have a 16 byte length.
To convert between a NUMERIC and a VARBINARY, the NUMERIC source must have a value that can also be cast as a BIGINT.
To convert from a VARCHAR to an ST_GEOMETRY, the VARCHAR source must represent a valid geometry in either WKT or EWKT format.
To convert from a VARBINARY to an ST_GEOMETRY, the VARBINARY source must represent a valid geometry in WKB format.
When casting from a WKB or WKT formatted source to an ST_GEOMETRY, an SRID of 0 is assigned to the ST_GEOMETRY value. When
casting from an ST_GEOMETRY, VARCHAR values are formatted in EWKT and VARBINARY values are formatted in WKB.
The following function ensures a string is used as a date:
SELECT CAST( '2000-10-31' AS DATE );
The value of the expression 1 + 2 is calculated, and the result is then cast into a single-character string.
SELECT CAST( 1 + 2 AS CHAR );
You can use the CAST function to shorten strings:
SELECT CAST ( 'Surname' AS CHAR(5) );
Casting between VARCHAR and ST_GEOMETRY is usually implicit. For example, the following statement adds values to ST_GEOMETRY
columns using the ST_POINT function and a VARCHAR. Each value is implicitly cast to an ST_GEOMETRY data type consistent with
the table columns, but results still appear as VARCHAR.
INSERT INTO T1 VALUES (2, ST_POINT(1,2,0), 'SRID=2163;Point(1 2)');