Alphabetically order a field with comma separated values-CodePudding

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.

CodePudding user response：

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

CodePudding user response：

Here is how it's done in Talend :

output would be :

|=------------------------------------------------------------=| |country | |=------------------------------------------------------------=| |Cabo Verde , Morocco , North Atlantic Ocean , Portugal , Spain| '--------------------------------------------------------------'