MySQL "like" (false positive)

Question

I have a table in my database, with a field named "tags", e.g. iOS, Android, Java, JavaScript, etc.. And I want to select all the items in this table, who match some tag, e.g

id | name | tags

-- | ------- | -----

1 | name1 | iOS,Android

2 | name2 | JavaScript,CSS

3 | name3 | HTML,Java

now, if I want only the items who have the tag 'Java' (only the one with id=3), i do this:

SELECT * FROM posts WHERE tags LIKE '%Java%';

but, as you imagine, it returns me the second (JavaScript) and the third (Java) items..

How can I do to only return the third?

Answer 1

In MySQL, the best solution is find_in_set():

SELECT *
FROM posts
WHERE find_in_set('Java', tags) > 0;

In MySQL and other databases, you can also do this with like, but you need to put delimiters around everything:

SELECT *
FROM posts
WHERE concat(',', tags, ',') like '%,Java,%';

The delimited prevent confusion with similar tags (well, confusion that doesn't involve commas).