PostgreSQL sorting in column-CodePudding

I have some column with few names separated by ,. How to sort them in alphabetical order?

Example row:

| xxx,aaa,hhh,bbb |  1111 |  222  |

I want to get:

| aaa,bbb,hhh,xxx |  1111 |  222  |

CodePudding user response：

You will need to write a function to do that:

create function sort_csv(p_input text)
  returns text
as
$$
  select string_agg(u.element, ',' order by u.element)
  from unnest(string_to_array(p_input, ',')) as u(element);
$$
language sql;

Then use it like this:

select sort_csv(csv_column), other_column
from badly_designed_table;

CodePudding user response：

Using a function is much cleaner, but if you cannot create objects, then this will work:

select array_agg(value order by value) as stuff, b.col2, b.col3
  from bad_idea b
       cross join lateral regexp_split_to_table(stuff, ',') as s(value)
 group by b.col2, b.col3;

db<>fiddle here