Using SQL split

Question

In one of my tables I am storing GroupIDs in a format of: '2#3#5' which means a user belongs to groups 2, 3 and 5. How can I amend my query to state g.GroupID IN (2,3,5) ? Where it's getting 2,3,5 FROM

SELECT GroupIDs FROM BW_Staff WHERE StaffCode = ''

// Output: 2#3#5

g.GroupID IN (2, 3, 5) 2, 3, 5 values needs to be from this query so it needs to be splitted by hash?

SELECT g.Name, u.StaffCode,  SUBSTRING(u.Perms, 47, 1) AS Staff_Perm_Grant,
       SUBSTRING(g.Perms, 47, 1) AS Group_Perm_Grant 
FROM BW_Groups g,
     BW_Staff u
WHERE g.GroupID IN (2, 3, 5) and u.StaffCode = 'KAA'

thanks for any help

No join condition? Add sample table data and the expected result - as well formatted text. — jarlh, Nov 24 '16 at 10:59
I would create another table "Groups" with UID, UserId, GroupNumber instead of a concatenated string field. — LocEngineer, Nov 24 '16 at 11:00

score 0 · Answer 1 · edited May 23 '17 at 12:24

Despite the fact that it probably is a badly designed database (you should be creating a 'Groups' table with a many-to-many relation to the 'Staff' table), so you can join both.

It is possible to split the string in SQL Server 2016, using string_split(<field>, <delimiter>). How to split a comma-separated value to columns

In MySQL you have no such luck but it's possible to create a function to do the same thing: How to split the name string in mysql?

Using SQL split

1 Answers1