try_cast function

Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime 10.4 LTS and above

Returns the value of sourceExpr cast to data type targetType if possible, or NULL if not possible.

Syntax 

try_cast(sourceExpr AS targetType)

Arguments

  • sourceExpr: Any castable expression.

  • targetType: The type of the result.

Returns

The result is of type targetType.

This function is a more relaxed variant of cast function which includes a detailed description.

try_cast differs from cast function by tolerating the following conditions as long as the cast from the type of expr to type is supported:

  • If a sourceExpr value cannot fit within the domain of targetType the result is NULL instead of an overflow error.

  • If a sourceExpr value is not well formed or contains invalid characters the result is NULL instead of an invalid data error.

Exception to the above are:

  • Casting to a STRUCT field with NOT NULL property.

  • Casting a MAP key.

Examples 

> SELECT try_cast('10' AS INT);
 10

> SELECT try_cast('a' AS INT);
 NULL