Q:

Splitting string and sorting in postgresql

I have a table in postgresql with a text column that has values like this:
column
-----------
CA;TB;BA;CB
XA;VA
GA;BA;LA

I want to sort the elements that are in each value, so that the query results like this:
column
-----------
BA;CA;CB;TB
VA;XA
BA;GA;LA

I have tried with string_to_array, regexp_split_to_array, array_agg, but I don't seem to get close to it.
Thanks.

A:

I hope this would be easy to understand:
WITH tab AS (
    SELECT
        *
    FROM
        unnest(ARRAY[
            'CA;TB;BA;CB', 
            'XA;VA', 
            'GA;BA;LA']) AS txt
)
SELECT
    string_agg(val, ';')
FROM (
    SELECT
        txt,
        regexp_split_to_table(txt, ';') AS val
    FROM
        tab
    ORDER BY 
        val
) AS sub
GROUP BY
    txt;

First I split values to rows (regexp_split_to_table) and sort. Then group by original values and join again with string_agg.
Output:
BA;CA;CB;TB
BA;GA;LA
VA;XA

