Standard SQL (Bigquery) Deduplicate comma-separated lists

I have a BigQuery table col with values like

"d,b"
"b,c"

and I want to aggregate it to say "d,b,c" and preferably sort it alphabetically to "b,c,d".

Any idease please? Thanks!

Answer

You would use split():

with t as (
    select 'd,b' as col union all
    select 'b,c'
)
select string_agg(distinct el order by el)
from t cross join
     unnest(split(col, ',')) el;

That said, I would recommend that you use arrays instead of strings for storing such values.