Type conversion functions

SQL Function Description

CAST(value AS type)

Returns a new value being cast to type type. For example:

Returns

CAST('42' AS INT)

42

CAST(NULL AS VARCHAR)

NULL

(of type VARCHAR)

TRY_CAST(value AS type)

Like CAST, but in case of error, returns NULL rather than failing. For example:

Returns

TRY_CAST('42' AS INT)

42

TRY_CAST(NULL AS STRING)

NULL

(of type STRING)

TRY_CAST('non-number' AS INT)

NULL

(of type INT)

COALESCE(TRY_CAST('non-number' AS INT), 0)

0

(of type INT)

TYPEOF(input)

TYPEOF(input, force_serializable)

Returns the string representation of input expression’s data type.

By default, the returned string is a summary string that might omit certain details for readability.

If force_serializable is set to TRUE, the string represents a full data type that can be persisted in a catalog. Note that especially anonymous, inline data types have no serializable string representation. In this case, NULL is returned.

For example:

SELECT CAST('42' AS VARCHAR),
       TYPEOF(CAST('42' AS VARCHAR)),
       TRY_CAST('non-number' AS INT),
       TYPEOF(TRY_CAST('non-number' AS INT));

returns:

+--------+-----------------+--------+--------+
¦     42 ¦ STRING NOT NULL ¦ <NULL> ¦    INT ¦
+--------+-----------------+--------+--------+