CODEWITHSUNDEEP
mysql
Michal
Michal

Reputation: 3368

Select rows where variable is found in a comma delimited cell

I have a a table that has a cell on each row that has several comma-delimited values in it, like TAGS=1,2,3,4,5. How can I select all rows that contain for example the TAG 5?

I have tried SELECT * FROM videos WHERE tags LIKE %$id%, however I get into problems if $id=2 for example, as 12 or 22 get selected too...

Any ideas please?

Upvotes: 0

Views: 522

Answers (3)

Jakub Kania
Jakub Kania

Reputation: 16487

Use FIND_IN_SET() http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_find-in-set select * from videos where FIND_IN_SET('22',tags)>0; http://sqlfiddle.com/#!2/5a699/1

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269873

You want to set up the where statement as:

where concat(',', tags, ',') like concat('%,', 5, ',%')

In your case, this would be:

where concat(',', tags, ',') like concat('%,', $id, ',%')

That is, you want to be sure that the delimiters are around the value you are looking for. So, the right-hand side is evaluated as '%,5,%'. Then, you need to be sure that every element of the list is surrounded by delimiters, include the first and last elements.

Upvotes: 1

Duniyadnd
Duniyadnd

Reputation: 4043

Ooooh, I did this a long long time ago - and it was the biggest mistake in my life when I thought I was a genius on how I attacked a problem. You see, "Like" is a pretty expensive comparison operator, takes a while for anything to be looked up, especially the minute you put the percentages in there.

You're better off creating a separate table that links the different id to each row, the lookups will be insanely faster.

However, you want to solve the issue above. I'd suggest updating your column to have commas at the beginning or the end, and then search for anything that has a comma before or after it.

e.g.

LIKE '%,12,%'

Upvotes: 1

Related Questions