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 |
|---|---|
ANY_VALUE
|
|
ARRAY_AGG
|
Collation on input arguments are propagated as collation on the array element. |
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
|
|
MAX
|
|
MIN
|
|
NORMALIZE
|
|
NORMALIZE_AND_CASEFOLD
|
|
NULLIFERROR
|
|
REPEAT
|
|
REPLACE
|
|
REVERSE
|
|
RIGHT
|
|
RPAD
|
|
SOUNDEX
|
|
SPLIT
|
Collation on input arguments are propagated as collation on the array element. |
STRING_AGG
|
|
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. |
UNNEST
|
Collation on the input array element is propagated to output. |
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.
