470,822 Members | 1,415 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,822 developers. It's quick & easy.

How to find string occurrences, sort them randomly by key and assign them to new attr

1 Bit
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:

Find strings from a fixed list ('hb','am','dr','ac') in the source_string
Create 3 new attributes and assign the found strings to them randomly but fixed (no difference after query re-execution)
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 | cc
3245 | am;xa;sp;cc; | cc | am |
9831 | dr;hb;am;ei; | hb | dr | am

My thought process:

I wanted to return the strings that occurred per row -> 1355: HB, am,dr, cc, (no idea how)
Rank them based on the key to having it randomly (maybe with rank() and mod())
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 that is not desirable.
Jan 23 '21 #1
0 1630

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

13 posts views Thread by Gram | last post: by
3 posts views Thread by Peter Afonin | last post: by
6 posts views Thread by bobueland | last post: by
3 posts views Thread by shapper | last post: by
reply views Thread by Allan | last post: by
reply views Thread by mihailmihai484 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.