CODEWITHSUNDEEP
mysqlsqlcsv
Manh Le
Manh Le

Reputation: 15

Select where an column with comma separated values contains an ID

I have two tables: tbl_post and tbl_tags.

In tbl_post, the field tags contains IDs from tbl_tags as an array. Example: '1,3,15,20' or '4,15,6,21'

I want to select records from tbl_post whose field tags contains an ID from tbl_tags (Example: '15').

How can I do this?

Upvotes: 0

Views: 2826

Answers (2)

Satyendra Yadav
Satyendra Yadav

Reputation: 107

working query for all cases :

SELECT * FROM `tbl_post` WHERE find_in_set('15',tbl_post.tags)

or 

SELECT * FROM `tbl_post` WHERE find_in_set('15',tbl_post.tags) <> 0

Upvotes: 1

ske57
ske57

Reputation: 607

What about: 

SELECT * FROM tbl_post WHERE tbl_post.tags LIKE '%15%';

OR

SELECT * FROM tbl_post WHERE Contains(tbl_post.tags, '15');

As per your comment, you could try this 

DECLARE @id INT = 15
DECLARE @stringId VARCHAR(50) = CAST(@id AS VARCHAR(50))

SELECT * 
FROM tbl_post 
WHERE tbl_post.tags = @stringId -- When there is only 1 id in the table
OR tbl_post.tags LIKE @stringId + ',%' -- When the id is the first one
OR tbl_post.tags LIKE '%,' + @stringId + ',%' -- When the id is in the middle
OR tbl_post.tags LIKE '%,' + @stringId -- When the id is at the end

Referenced from this SO post

Upvotes: 0

Related Questions