Return only ALL CAPS strings in BigQuery

Pretty simple question, specific to BigQuery. I’m sure there’s a command I’m missing. I’m used to using “collate” in another query which doesn’t work here.

     email           
| --------           |
| [e-mail]     | 
| [e-mail]     | 
| [e-mail]    | 
| [e-mail]    | 

Desired return:

[e-mail],[e-mail]

Answer

Consider below

select *
from your_table
where upper(email) = email           

If applied to sample data in your question – output is

In case you want the output as a comma separated list – use below

select string_agg(email) emails
from your_table
where upper(email) = email

with output

You can use below cte (which is exact data sample from your question) for testing purposes

with your_table as (
  select '[e-mail]' email union all
  select '[e-mail]' union all
  select '[e-mail]' union all
  select '[e-mail]' 
)