MySQL select where tag contains a substring-CodePudding

I'm creating a blog, in which I have to display all posts which contains a specific tag.

Example Table

Post            |         Tags
----------------------------------
Post 1          | tag1, tag2, tag3
Post 2          | xtag1, tag3
Post 3          | tag2

I want to select every row where tag2 is present in tags string with php and MySQL. I can do that by MySQL LIKE clause but it'll also selects the xtag2, because tag2 is in it. Is there any way to do this. It's just a demo table, the real table is going to be very large.

Thank You

CodePudding user response：

According to your data sample (comma separated and having space after comma), the following query will get the data that you want. This query example is searching for 'tag1' value.

SELECT 
  Post, tag 
FROM (
  SELECT 
    Post, tag, CONCAT(', ', tag) AS myTag FROM posts 
  ) AS t1 
WHERE POSITION( CONCAT(', ', 'tag1') IN myTag )

The key to answer is adding ', ' in front of both the value of tag field and the search tag value('tag1' in this example).

CodePudding user response：

Query :

SELECT * from `blog` WHERE FIND_IN_SET('tag2',Tags)

Here,

tag2 is the keyword and Tags is the column name.

It won't select the row with the keyword 'xtag2' but will only select every row with the keyword 'tag2'.

FIND_IN_SET('keyword',Column_Name)