Mysql select row from Comma separated value

Some Time We Have a table column which store its value as comma separated

For example Two Tables [Post] and [Categories]

[Post]

Post_id Title Category_id
1 A 1,2
2 B 11,2
3 C 11,3

[Categories]

Category_id Category_name
1 A1
2 A2
3 A3
……………….. ……………………
11 A11

So if we want to find exact row of category from post table with category_id=11;

[ Workable Query ]

Then We have to Write Following Query:

=> Select * from Post Where FIND_IN_SET(11,Category_id);

So,why should We do this for?

[  Non Workable Query ]

Step 1

We Can find it with the query:

=> Select *from Post Where Category_id=11;

Not Work Because There is comma separate and not exact only the numbers there.

Step 2

=> Select *from Post Where Category_id Like ‘%11%’;

it Also Bring The The row That Contain 1 as value

Step 3

=> Select  *from Post Where Category_id REGEXP ‘[11]’;

Same Result Come  of Step 3

 

So the function is FIND_IN_SET(Value,Column);

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s