Mysql how to handle a single row with multiple items

Question

I have an excel CSV file that I have imported into a database. There is a field called groups that includes all the groups a specific person belongs to. The groups are separated by a | (Pipe) character. I would like to run through this field for each person searching for the group to see if they belong but I am having a hard time figuring out how to create a loop to read through all of them.

Table example

------------------------------------ 
|Name   |      Groups               |
------------------------------------
|Bob    | Cleaning|Plumber          |
|Sue    | Admin|Secretary|Pay_role  |
|Joe    | Engineer                  |
|Frank  | Plumber|Admin             |
|James  | Plumber|Carpenter         |

I figured out how to grab the first group before the | but I don't know how to read each field after that

SELECT substring(Groups,1,((instr(Groups,'|')-1))) as ExtractString

from DB_groups

In the future I would like to add people to a group and delete people from a group so I am looking for a query that will allow me to see everyone's group like:

Sue | Admin
Sue | Secretary
Sue | Pay_r

ole

Maybe there is a better way to do this but the CSV file has 25k records so I am kinda stuck with what is already in there. Thanks for the help.

First normalise your schema. – Strawberry Mar 08 '18 at 20:33 — Strawberry, Mar 08 '18 at 20:33

Gordon Linoff · Accepted Answer · 2018-03-09T13:07:40.577

1

One method is something like this in SQL:

select name, substring_index(groups, '|', 1)
from t
union all
select name, substring_index(substring_index(groups, '|', 2), '|', -1)
from t
where groups like '%|%'
union all
select name, substring_index(substring_index(groups, '|', 3), '|', -1)
from t
where groups like '%|%|%'
union all
select name, substring_index(substring_index(groups, '|', 4), '|', -1)
from t
where groups like '%|%|%|%';

This works with lists up to four long, but it can be easily extended to more.

Here is a SQL Fiddle for this method.

Or, a shorter way to handle this:

select name, substring_index(substring_index(groups, '|', n.n), '|', -1) as grp
from t cross join
     (select 1 as n union all select 2 union all select 3 union all select 4
     ) n
where n.n >= (length(groups) - length(replace(groups, '|', '')))

To add more groups, just increase the size of n.

Here is a SQL Fiddle for this version.

edited Mar 09 '18 at 13:07

answered Mar 08 '18 at 19:47

Gordon Linoff

1,242,037
58
646
786

The first query only gave me the substring_index(groups, '|', 1) field and not any of the others. I can break the query apart and get the other sections but I can't seem to combine it. The second query doesn't run. I swapped out the n for the number but I was confused at if it was supposed to be 4.4 where it has an n.n – T C Mar 08 '18 at 20:51
@TC . . . Are you sure that the separator is a vertical bar? – Gordon Linoff Mar 09 '18 at 13:01
@TC . . . The first version worked fine. I fixed the second version and added SQL Fiddles for both. – Gordon Linoff Mar 09 '18 at 13:11
Yes the separator is a vertical bar. I am able to search for it and it pulls all the results I am expecting. – T C Mar 09 '18 at 15:49
Okay I kept the query with the n.n value and did not switch it out for a number and now it runs better but I am still missing the first group before the | so I am working on figuring out why – T C Mar 09 '18 at 19:05
@TC . . . The SQL Fiddles are returning all the values. – Gordon Linoff Mar 10 '18 at 02:39

score 0 · Answer 2 · answered Mar 08 '18 at 19:53

0

I work with SQL Server 2014 so I, unfortunately, I can't use these functions but this issue is pretty similar to what I found here.

answered Mar 08 '18 at 19:53

not_ur_avg_cookie

303
4
15

score 0 · Answer 3 · answered Mar 22 '18 at 13:09

Okay I figured out how to do a nested loop to complete this.

while ($row = mysqli_fetch_array($result)) {

//echo "Im' in the Loop"; echo '' . $row['First_Name'] . '' ;

echo    '<td width="70">' . $row['Middle_Initial'] . '</td>';

echo    '<td width="70">' . $row['Last_Name'] . '</td>';

echo    '<td width="70">' . $row['External_ID'] . '</td>';

//Output all groups with loop
$str = $row['Groups'];
$wordChunks = explode("|", $str);
echo    '<td width="10">';
for($i = 0; $i < count($wordChunks); $i++){
 echo "$wordChunks[$i]" . '<br />';
}
'</td>';

echo    '<td width="70">' . $row['User_ID'] . '</td>';

echo '<tr>';    
} // End of Loop

Mysql how to handle a single row with multiple items

3 Answers3