How to get a single value form multiple values in a Mysql field

Question

I have created a mail table where i have the following fields.

id    mail_to    mail_subject    mail_message
1     6,9,10     Test Mail       Test Message
2     4,8,6      Test Mail       Test Message

Values stored in mail_to field are id of users. I want to display all the mail where mail_to is 6. How to use the where condition in this case.

I tried to achieve the result with this query but it did not work.

SELECT * FROM tbl_profile_inbox WHERE mail_to = '6' ;

Can you guys help on this one.

SUBSTRING may lead towards wrong output in some particular cases like mail_to = 9,66,10 — 1000111
– 1000111, Commented Apr 25, 2016 at 7:44

mitkosoft · Accepted Answer · 2016-04-25 07:44:33Z

3

You can use FIND_IN_SET() for that:

SELECT * FROM tbl_profile_inbox WHERE FIND_IN_SET('6',mail_to)

answered Apr 25, 2016 at 7:44

mitkosoft

5,3361 gold badge15 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

This is a nice solution if you don't want to change your table design. But still I would suggest change your table design.

Why do i need to change my table design. Is there anything wrong in this design.

Dipanwita Kundu · Accepted Answer · 2016-04-25 07:57:16Z

0

you can use REGEXP also.

SELECT * FROM tbl_profile_inbox WHERE mail_ to REGEXP '[[:<:]]6[[:>:]]'

answered Apr 25, 2016 at 7:57

Dipanwita Kundu

1,6451 gold badge10 silver badges14 bronze badges