How to query MySQL if data in one field look like 32;13;3;33 and the condition is where 3

Question

I have a table in which one column is filled with data like 32;3;13;33;43

so

SELECT * FROM table;

gives something like

name ids
vegetables 13;3;63
fruits 37;73;333

When I'm querying MySQL like

SELECT * FROM table WHERE ids LIKE '%3%'

it gives me both records but obviously I want only this containing 3.
How to query MySQL correctly?

...and that's one of the about 20 reasons why you should not store comma separated values in a column. See this answer: http://stackoverflow.com/a/3653574/447489 — fancyPants, Mar 06 '14 at 11:18
It's not me. The data are there already. Sometimes world is not perfect you know? — Kornel, Mar 06 '14 at 11:24

score 1 · Accepted Answer · answered Mar 06 '14 at 11:21

1

Try to use:

SELECT * FROM table WHERE CONCAT(';',ids,';') LIKE '%;3;%'

answered Mar 06 '14 at 11:21

valex

score 1 · Answer 2 · answered Mar 06 '14 at 11:24

1

You will need to cover the case where it's the first in the list and the last.

SELECT * FROM table WHERE ids LIKE '%;3;%' OR LIKE '%;3' OR LIKE '3;%'

answered Mar 06 '14 at 11:24

stuartf

score 0 · Answer 3 · answered Mar 06 '14 at 11:38

0

You can use FIND_IN_SET, if you replace the ; with a , before checking the value:0

SELECT * 
FROM table 
WHERE FIND_IN_SET('3', REPLACE(ids, ';', ','))

answered Mar 06 '14 at 11:38

Kickstart

3 Answers3