CODEWITHSUNDEEP
mysql
Anju L S
Anju L S

Reputation: 37

How to search with comma separated values in mysql

I have a string comnid='1,2,3' and in need to find the rows with these id eg:

i have a table like this

colour     comnid
----------------------
black       1
blue        2
green       3
yellow      4

and i need to return the rows which comnid are 1,2,3

Upvotes: 0

Views: 846

Answers (2)

Flash Thunder
Flash Thunder

Reputation: 12036

Would be ... WHERE comnid IN(1,2,3)

Please note that a query may be vulnerable to sql injections. You need to prepare it correctly, depending of what support language / lib you are using. In PHP would use function mysqli_real_escape_string().

Upvotes: 1

Akina
Akina

Reputation: 42622

CREATE TABLE colours_table
SELECT 'black'   colour,    1 comnid UNION ALL
SELECT 'blue'          ,    2        UNION ALL
SELECT 'green'         ,    3        UNION ALL
SELECT 'yellow'        ,    4        ;

SELECT * FROM colours_table;

colour | comnid
:----- | -----:
black  |      1
blue   |      2
green  |      3
yellow |      4

SET @comnid = '1,2,3';

SELECT GROUP_CONCAT(colour)
FROM colours_table
WHERE FIND_IN_SET(comnid, @comnid);

| GROUP_CONCAT(colour) |
| :------------------- |
| black,blue,green     |

db<>fiddle here

If you need in separate rows then simply do not use GROUP_CONCAT:

SELECT colour
FROM colours_table
WHERE FIND_IN_SET(comnid, @comnid);

Upvotes: 1

Related Questions