467,877 Members | 1,181 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

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

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

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.
4 Weeks Ago #1
  • viewed: 1292
Share:

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
By using this site, you agree to our Privacy Policy and Terms of Use.