Changes an expression's data type, written CAST(expr AS type) or with the :: operator. When ANSI mode is on, a conversion that fails is an error; try_cast gives null in that case.
Read more: Databricks docs
In the Ultra Transcenders books
Each book explains CAST in context, with comparison tables and the common traps.
Terms in this definition
- Data type
A rule limiting what a column may contain, for example INT for integers, DECIMAL for numbers with fractions or VARCHAR for text of varying length. Which types exist depends on the database product.
- CAST
Converts a value to a different data type using the syntax
CAST(expr AS type); this ANSI-standard T-SQL function errors out if the conversion can't be done. - ANSI mode
A stricter Spark SQL behaviour, enabled by default since Apache Spark 4.0 (so also in Fabric Runtime 2.0), where a bad cast or an integer overflow fails the query rather than quietly producing null. Functions such as
try_cast,try_addandtry_dividekeep the old null result, while switching ANSI mode off should be treated as a temporary workaround. - NULL
Marks that a row has nothing in a particular column, as when a person has no middle name. Columns defined NOT NULL can never be left empty.