Comparing strings in sql

Question

I have a list of string values for example ('a', 'b', 'c', 'd', 'e'). Now I have a database lets say (T1) having two columns for example (user and permission). Every user has some permissions. For example.

|user | permission|
| user1 | a |
| user2 | a,b,f |
| user3 | b,c |
| user4 | e,d |
| user5 | f,g |

I want to write a query which can retrieve the users who are using those permissions. What I tried until now is below.

select * from T1 where permissions IN ('a', 'b', 'c', 'd', 'e')

After running this I only get user1 nothing else. But the answer should be user2 and others also because they are using permission a also.

Note: This is just an example. The strings I have are more than 25 and the database has more than 100 values.

• nbk: mysql can't really handle coma delimiter values, so you have always first convert those stirngs into rows and then search for it like it is intended, and next time don't save thme in that form if you want to search for it. also take a look for normalized tables
• Akina: You must convert CSV (column values, checklist values, or both) to the rowset one value per row, then compare.

Answer 1

What you can do is something like this (see the fiddle here):

Table:

CREATE TABLE t
(
 user VARCHAR (10) NOT NULL,
 permission VARCHAR (10) NOT NULL
);

Data:

INSERT INTO t VALUES
('user1', 'a'),
('user2', 'a, b, f'),
('user3', 'b, c'),
('user4', 'e, f'),
('user5', 'f, g');

Query:

SELECT * FROM t
WHERE permission LIKE '%a%'
OR permission LIKE '%b%'
OR permission LIKE '%c%'
OR permission LIKE '%d%'
OR permission LIKE '%e%';

Result:

user permission
user1 a
user2 a, b, f
user3 b, c
user4 e, f

user5 is excluded because his permissions are 'f,g' and these don't appear in the sequence a-e.

p.s. welcome to the forum!

• Farukh Khan: Thanks for the answer, but, I don't have only a small amount of data. LIKE is super easy and great idea when you want to match just a number of strings. I have more than 25 strings. !
• Vérace: @FarukhKhan MySQL could easily cope with a "lage" number - certainly well above 25. AFAIK in MySQL, the IN clause is internally converted to a series of ORs anyway! In any case, I'm glad you found my answer helpful!