I need to check if a value is in a column in the DB. My column has the content separated by commas, like this: 1,2,3,4,5. So, I would like to know how to check if value 2, for example, is in some row in the SQL column.
Select everything from mytable when 1 is in my_column
Table structure
:
Actually your table was designed incorrectly. The correct one would be to have a support table with the codes and a link to that table. However if there is no possibility to change the table you can use the following options:
1) DISTINCT + SUBSTRING_INDEX + Support Table
Applying the theory this answer we have the following query for proposed search:
SELECT *
FROM (SELECT DISTINCT t.id,
t.id_busca,
t.players,
TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(t.players, ',', n.numero), ',', -1)) AS ocorrencia
FROM tabela t
CROSS JOIN numeros n
ORDER BY t.id, n.numero) x
WHERE x.ocorrencia = 2
You can check out the SQL Fiddle .
In the above solution we created a support table (called numeros ) with the possible positions. Note that the numbers are not the values , but the positions and with CROSS JOIN with this table we make SUBSTRING_INDEX , thus allowing all positions comma) are covered. Note that if only 10 numbers were used in the example, however if there are more possible positions the support table must be filled according to the possibilities (always for more).
1) FIND_IN_SET
Another solution is to use the FIND_IN_SET :
SELECT t.id,
t.id_busca,
t.players
FROM tabela t
WHERE FIND_IN_SET(2, t.players)
See working in SQL Fiddle .
References: