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 |
-
3Why do you have comma-separated values in a column to start with? In a database you only do this, when you want to remain oblivious to the strings' content. But then you wouldn't want to sort them. If you are interested in the separate parts, then you've already violated database first normal form. My advice: Either stay oblivious in your database and care about the parts in an app outside the db, or fix the datamodel.Thorsten Kettner– Thorsten Kettner2022-07-11 10:09:16 +00:00Commented Jul 11, 2022 at 10:09
-
What have you tried so far? What have you found on the Internet or stackoverflow to help you with this? Where have you got stuck?Thorsten Kettner– Thorsten Kettner2022-07-11 10:13:04 +00:00Commented Jul 11, 2022 at 10:13
-
1That's a horrible data model to begin with. Do you have any chance to fix that before you proceed?user330315– user3303152022-07-11 10:15:25 +00:00Commented Jul 11, 2022 at 10:15
Add a comment
|
2 Answers
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;
Comments
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
Comments
