Alphabetically order a field with comma separated values

Question

I am currently working on a Talend flow and I'm having an issue ordering a field which contain comma separated values. This field is about countries and it can contain different countries. I'd like to know if there's a way to alphabetically order those values inside the field.

I don't know if it's easier to do it with Talend or directly with an sql query.

Here is an example of a wrong value of this field: "Portugal,Cabo Verde,Morocco,North Atlantic Ocean, Spain" and I'd like it to be alphabetically ordered if possible.

Answer 1

This would be so much easier with a properly normalized data model.

To get a sorted string, you need to first unnest the elements and then aggregate them back into a sorted string.

select other_columns
       (select string_agg(country, ',' order by country)
        from unnest(string_to_array(countries, ',')) as t(country)
       ) as countries_sorted
from the_table

You can put that into a function to make your life easier:

create function sort_csv_value(p_input text)
  returns text
as
$$
   select string_agg(word, ',' order by word)
   from unnest(string_to_array(p_input, ','));
$$
language sql
immutable;

Then you can use it like this:

select other_columns
       sort_csv_value(countries) as countries_sorted
from the_table