Select a row that contains the exact number in the column with a set of numbers separated by a comma

Question

Perhaps the answer is very simple, but I cannot find the correct MySQL query that does what I want.

I have a user table:

| id_user | name | action_type | +---------------------------------+ | 1 | joshua | 1,13,12,40 | | 2 | joshua | 2,8 | 

And I want to select only rows that have the exact number in the action_type column.

action_type is stored in MySQL as TEXT.

I tried this:

SELECT * FROM user WHERE action_type LIKE '%2%'

But he selected lines with 12, which is not what I want: (

This may be possible with the IN operator, but I could not find the correct way to use it.

Answer 1

Are you looking for FIND_IN_SET

 SELECT * FROM user WHERE FIND_IN_SET( '2', action_type ) 

SQL Fiddle DEMO

UPDATE

Just to mention this, it is also possible.

 SELECT * FROM user WHERE FIND_IN_SET( 2, action_type ) 

MySQL will do automatic conversion to char

Answer 2

Include the delimiter in the LIKE :

 SELECT * FROM user WHERE action_type LIKE '2,%' OR action_type LIKE '%,2,%' OR action_type LIKE '%,2' 

Note that I had to use two additional LIKE clauses to cover cases where an element is at the beginning or end of a line.

Answer 3

Try

 SELECT * FROM user WHERE CONCAT( ',', action_type, ',' ) LIKE '%,2,%'; 

• correct syntax sir rufo