Working with collation

GoogleSQL for Bigtable supports collation. You can learn more about collation in this topic.

About collation

Collation determines how strings are sorted and compared in collation-supported operations.

Operations that propagate collation

Collation can pass through some query operations to other parts of a query. When collation passes through an operation in a query, this is known as propagation. During propagation:

  • If an input contains no collation specification or an empty collation specification and another input contains an explicitly defined collation, the explicitly defined collation is used for all of the inputs.
  • All inputs with a non-empty explicitly defined collation specification must have the same type of collation specification, otherwise an error is thrown.

GoogleSQL has several functions, operators, and expressions that can propagate collation.

Functions

These functions let collation propagate through them:

Function Notes
ARRAY_FIRST
ARRAY_LAST
ARRAY_OFFSET
ARRAY_OFFSETS
ARRAY_SLICE
ARRAY_TO_STRING Collation on array elements are propagated to output.
CONCAT
FORMAT Collation from format_string to the returned string is propagated.
FORMAT_DATE Collation from format_string to the returned string is propagated.
FORMAT_DATETIME Collation from format_string to the returned string is propagated.
FORMAT_TIME Collation from format_string to the returned string is propagated.
FORMAT_TIMESTAMP Collation from format_string to the returned string is propagated.
GREATEST
LEAST
LEFT
LOWER
LPAD
NORMALIZE
NORMALIZE_AND_CASEFOLD
NULLIFERROR
REPEAT
REPLACE
REVERSE
RIGHT
RPAD
SOUNDEX
SPLIT Collation on input arguments are propagated as collation on the array element.
SUBSTR
UPPER

Operators

These operators let collation propagate through them:

Operator Notes
|| concatenation operator
Array subscript operator Propagated to output.
STRUCT field access operator When getting a STRUCT, collation on the STRUCT field is propagated as the output collation.

Expressions

These expressions let collation propagate through them:

Expression Notes
ARRAY When you construct an ARRAY, collation on input arguments is propagated on the elements in the ARRAY.
CASE
CASE expr
IF
IFNULL
NULLIF
STRUCT When you construct a STRUCT, collation on input arguments is propagated on the fields in the STRUCT.

Collation specification details

A collation specification determines how strings are sorted and compared in collation-supported operations. Binary collation is used by default and indicates that the operation should return data in Unicode code point order. You can't set binary collation explicitly.