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.