How to select row if my column values in comma in MySQL?

Question

I have a table name myTable with rows like below:

*--------------------------------------------*
| S.No. | user_id  | Event Name | Event Date |
*--------------------------------------------*
|  101  |  9,19    | Juniorun   | 25-June-19 |
*--------------------------------------------*
|  102  |  9,20    | Juniorun1  | 30-June-19 |
*--------------------------------------------*
|  103  |  9,21    | Juniorun2  | 27-June-19 |
*--------------------------------------------*
|  104  |  9,18,19 | Juniorun3  | 26-June-19 |
*--------------------------------------------*

Now I have user id 19 And through this, I want to select row whose user Id is 19.

I want:

*--------------------------------------------*
| S.No. | user_id  | Event Name | Event Date |
*--------------------------------------------*
|  101  |  9,19    | Juniorun   | 25-June-19 |
*--------------------------------------------*
|  104  |  9,18,19 | Juniorun3  | 26-June-19 |
*--------------------------------------------*

For this I am using the following query:

SELECT * FROM myTable WHERE user_id IN ('19');

But my query is not given any value. It is showing blank.

Better to normalize your database – Oto Shavadze Jun 13 '19 at 10:57 — Oto Shavadze, Jun 13 '19 at 10:57

score 1 · Answer 1 · answered Jun 13 '19 at 11:04

1

Given that user_id is stored as a String, you can use the LIKE operator (wouldn't recommend)

SELECT *
FROM myTable
WHERE user_id LIKE '%19%';

or, use FIND_IN_SET in the WHERE clause as,

SELECT *
FROM myTable
WHERE FIND_IN_SET('19', user_id) > 0;

answered Jun 13 '19 at 11:04

Johnson Sequeira

68
4

Arulkumar · Accepted Answer · 2019-06-13T11:03:23.687

0

Using FIND_IN_SET(), you can achieve the expected result:

SELECT * 
FROM myTable 
WHERE FIND_IN_SET('19', user_id);

Demo on db<>fiddle

edited Jun 13 '19 at 11:03

answered Jun 13 '19 at 10:56

Arulkumar

12,966
14
47
68

Perfectly working, I am not using > 0 I do not need this. Thanks buddy – Santosh Khatri Jun 13 '19 at 11:03
@SantoshKhatri You're Welcome. Yes `> 0` is not required. – Arulkumar Jun 13 '19 at 11:04

score 0 · Answer 3 · answered Jun 13 '19 at 11:00

0

try FIND_IN_SET

SELECT * FROM myTable WHERE FIND_IN_SET(19,user_id)

answered Jun 13 '19 at 11:00

Raj

11
3

score 0 · Answer 4 · answered Jun 13 '19 at 11:10

The FIND_IN_SET() function returns the position of a string within a list of strings. FIND_IN_SET(string, string_list) Parameter Description string Required. The string to search for string_list Required. The list of string values to be searched (separated by commas) Then your SQL will be

SELECT * FROM myTable WHERE FIND_IN_SET(19,user_id)

How to select row if my column values in comma in MySQL?

4 Answers4