How to compare the a single value among the comma seperated values in a column of a database using Select Query?

I have a value say for example "1" and I Have column "grp_id" in my db table "tbl_grp". This column "grp_id" contains the commaseperated values as example "1,2,3" I am looking for a way to compare my single value with column "grp_id".

right now I am using the below query:

"SELECT user_id, user_fname, u开发者_如何学JAVAser_lname FROM tbl_grp WHERE grp_id='1'";

Your data is not in First Normal Form. If you follow the rules for first normal form, you will have keyed access to all data. Some queries might still result in a table scan, but not this kind.

Don't use CSV for searchable items. Decompose this field into a 1NF equivalent.

It's not a many-to-many relationship as another answer said. It's a one-to-many.

You could you FIND_IN_SET:

SELECT user_id, user_fname, user_lname FROM tbl_grp WHERE FIND_IN_SET(1, grp_id);

But the correct way to do this, instead of storing a comma-separated list of ids in a column, is to have a many-to-many association between your users and groups:

user(user_id, user_fname, ...)
group(group_id, group_name, ...)
user_group(#user_id, #group_id) // you can have any number of (user_id, group_id) couples

You're looking for FIND_IN_SET:

SELECT user_id, user_fname, user_lname 
FROM tbl_grp 
WHERE FIND_IN_SET('1', grp_id);

Try This,

SELECT user_id, user_fname, user_lname FROM tbl_grp WHERE grp_id LIKE '1';

try i am no master in db, but best i can suggest this.