SQL: How to find string occurrences, sort them randomly by key and assign them to new attributes?

Question

I have the following sample data:

    key | source_string       
   ---------------------
   1355 | hb;am;dr;cc;         
   3245 | am;xa;sp;cc;       
   9831 | dr;hb;am;ei;              

What I need to do:

1. Find strings from a fixed list ('hb','am','dr','ac') in the source_string
2. Create 3 new attributes and assign the found strings to them randomly but fixed (no difference after query re-execution)
3. If possible no subqueries and all in one SQL SELECT statement

The solution should look like this:

    key | source_string | t_1 | t_2 | t_3
   ---------------------------------------
   1355 | hb;am;dr;cc;  | hb  | dr  |    
   3245 | am;xa;sp;cc;  | am  |     |   
   9831 | dr;hb;am;ei;  | hb  | dr  | am   

My thought process:

1. I wanted to return the strings that occurred per row -> 1355: hb,am,dr,cc, (no idea how)
2. Rank them based on the key to have it randomly (maybe with rank() and mod())
3. Assign the strings based on their rank to the new attributes. At key 1355 4 attributes match, but only 3 need to be assigned, so the one left has to be ignored. (Everything in Postgres)

In my current solution I created a rule for every case, which results in a huge query which is not desirable.

question from:https://stackoverflow.com/questions/65859400/sql-how-to-find-string-occurrences-sort-them-randomly-by-key-and-assign-them-t

Answer 1

One simple method is to split the string, reaggregate the matches to an array and use that for the results

select t.*,
       ar[1], ar[2], ar[3]
from t cross join lateral
     (select array_agg(el order by random()) as ar
      from regexp_split_to_table(t.source_string, ';') el
      where el in ('hb','am','dr','ac')
     ) s;

Here is a db<>fiddle;