"Conversion" includes, but is not limited to, casting and coercion.
- Casting is explicit conversion and uses the
- Coercion is implicit conversion, which Cloud Spanner SQL performs automatically under the conditions described below.
- There is a third group of conversion
functions that have their own function
names, such as
The table below summarizes all possible
CAST and coercion possibilities for
Cloud Spanner SQL data types. "Coercion To" applies to all expressions of a
given data type, (for example, a
|From Type||CAST to||Coercion To|
CAST(expr AS typename)
Cast syntax is used in a query to indicate that the result type of an expression should be converted to some other type.
CAST(x=1 AS STRING)
This results in
"false" for any other non-
Casts between supported types that do not successfully map from the original value to the target domain produce runtime errors. For example, casting BYTES to STRING where the byte sequence is not valid UTF-8 results in a runtime error.
When casting an expression
x of the following types, these rules apply:
|From||To||Rule(s) when casting
|INT64||FLOAT64||Returns a close but potentially not exact FLOAT64 value.|
|NUMERIC||Floating Point||NUMERIC will convert to the closest floating point number with a possible loss of precision.|
|FLOAT64||INT64||Returns the closest INT64 value.
Halfway cases such as 1.5 or -0.5 round away from zero.
|FLOAT64||STRING||Returns an approximate string representation.
|FLOAT64||NUMERIC||The floating point number will round
half away from zero. Casting a
Also supports casts from
Conversions are case-insensitive.
|STRING||NUMERIC||The numeric literal contained in the
All other values of
STRINGs are case-insensitive when converting to BOOL.
|STRING||BYTES||STRINGs are cast to BYTES using UTF-8 encoding. For example, the STRING "©", when cast to BYTES, would become a 2-byte sequence with the hex values C2 and A9.|
For example, the BYTES literal
An error occurs if
|ARRAY||ARRAY||Must be the exact same ARRAY type.|
|STRUCT||STRUCT||Allowed if the following conditions are met:
CAST, a query can fail if Cloud Spanner SQL is unable to perform
the cast. For example, the following query generates an error:
SELECT CAST("apple" AS INT64) AS not_a_number;
If you want to protect your queries from these types of errors, you can use
SAFE_CAST is identical to
CAST, except it returns NULL instead
of raising an error.
SELECT SAFE_CAST("apple" AS INT64) AS not_a_number; +--------------+ | not_a_number | +--------------+ | NULL | +--------------+
If you are casting from bytes to strings, you can also use the
SAFE_CONVERT_BYTES_TO_STRING. Any invalid UTF-8 characters are
replaced with the unicode replacement character,
SAFE_CONVERT_BYTES_TO_STRING for more
Casting hex strings to integers
If you are working with hex strings (
0x123), you can cast those strings as
SELECT '0x123' as hex_value, CAST('0x123' as INT64) as hex_to_int; +-----------+------------+ | hex_value | hex_to_int | +-----------+------------+ | 0x123 | 291 | +-----------+------------+ SELECT '-0x123' as hex_value, CAST('-0x123' as INT64) as hex_to_int; +-----------+------------+ | hex_value | hex_to_int | +-----------+------------+ | -0x123 | -291 | +-----------+------------+
Casting date types
Cloud Spanner SQL supports casting date types to/from strings as follows:
CAST(date_expression AS STRING) CAST(string_expression AS DATE)
Casting from a date type to a string is independent of time zone and is of the
YYYY-MM-DD. When casting from string to date, the string must conform to
the supported date literal format, and is independent of time zone. If the
string expression is invalid or represents a date that is outside of the
supported min/max range, then an error is produced.
Casting timestamp types
Cloud Spanner SQL supports casting timestamp types to/from strings as follows:
CAST(timestamp_expression AS STRING) CAST(string_expression AS TIMESTAMP)
When casting from timestamp types to string, the timestamp is interpreted using the default time zone, America/Los_Angeles. The number of subsecond digits produced depends on the number of trailing zeroes in the subsecond part: the CAST function will truncate zero, three, or six digits.
When casting from string to a timestamp,
string_expression must conform to
the supported timestamp literal formats, or else a runtime error
string_expression may itself contain a
If there is a time zone in the
string_expression, that time zone is used for
conversion, otherwise the default time zone, America/Los_Angeles, is used.
If the string has fewer than six digits, then it is implicitly widened.
An error is produced if the
string_expression is invalid, has more than six
subsecond digits (i.e. precision greater than microseconds), or represents a
time outside of the supported timestamp range.
Casting between date and timestamp types
Cloud Spanner SQL supports casting between date and timestamp types as shown in the conversion rules table.
CAST(date_expression AS TIMESTAMP) CAST(timestamp_expression AS DATE)
Casting from a date to a timestamp interprets
date_expression as of midnight
(start of the day) in the default time zone, America/Los_Angeles. Casting
from a timestamp to date effectively truncates the timestamp as of the default
Cloud Spanner SQL coerces the result type of an expression to another type if needed to match function signatures. For example, if function func() is defined to take a single argument of type INT64 and an expression is used as an argument that has a result type of FLOAT64, then the result of the expression will be coerced to INT64 type before func() is computed.
Additional conversion functions
Cloud Spanner SQL provides the following additional conversion functions: