mysql select of rows involving csv

Question

Hi, I have a table called txn in which a column called pid is a csv. I am using query in the following way:

SELECT * FROM txn where pid like '%,11%';

ie.,if pid=,1,2,7,11,4 , then this particular row should be selected.. But my problem is..since i am using

like

and if pid=,1,111,112 or something like this,then even though it does not have 11,this row will get selected..so this query does not solve my problem.. Can anyone help me with this??

Answer 1

SELECT * FROM txn where pid like '%,11,%' or pid like '%,11';

Answer 2

Your string always has a leading comma so add a trailing one too (e.g. pid=',1,111,112,') and then '%,11,%' will always be enough. You can even do this on the fly:

concat(pid,',') like '%,11,%'

Answer 3

SELECT * FROM txn where pid IN (1,2,7,11,4)

Hope that helps!