CODEWITHSUNDEEP
google-bigquery
Iouri Goussev
Iouri Goussev

Reputation: 378

BigQuery Collation

How can I set a collation order in BigQuery?

I want something like this

SELECT Place
FROM Locations
ORDER BY Place COLLATE "en_CA"

I can't find any documentation other than COLLATE is a reserved word in BigQuery.

BigQuery is sorting the following Strings in [a..zA..Z] order:

E.g.

Is there a way to ask BigQuery to sort in [aA..zZ] order?

Upvotes: 1

Views: 2190

Answers (2)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173086

Below example is for BigQuery Standard SQL

#standardSQL
create temp function collate_order(text string) as ((
  select string_agg(chr(1000 * ascii(lower(c)) - ascii(c)), '' order by offset)
  from unnest(split(text)) c with offset
));
with `project.dataset.Locations` as (
  select 'ant' as Place union all
  select 'Apple' union all
  select 'bee' union all
  select 'apple' union all
  select 'cat' union all
  select 'Banana' union all
  select 'Cantaloupe' 
)
select Place
from `project.dataset.Locations`
order by collate_order(Place)

with output

enter image description here

Forgot to mention - obviously you can extend this approach to handle unicode text by replacing ascii to unicode function

Upvotes: 1

Vibhor Gupta
Vibhor Gupta

Reputation: 699

You can try following query it will work for your requirement, it will sort data in [aA..zZ] order :-

SELECT Place
FROM Locations
ORDER BY upper(Place)

Upvotes: 0

Related Questions