Select value in field containing multiple values

I have a table (user) with a field named membergroupids which contains multiple values seperated by a comma.

The field type is varchar and will contain muiltiple member group id numbers.

for instance

Administrators group is 1
registered is 2
site users is 3
private group is 4

the field can contain this data in random entry.
one record could be   3,4
two could be             1,4
thre could be            4,3
4 could be                2,4


I am trying to figure out the sql statement that would select all records in the table that contain a 4 and ordered by userid.

examples I have tried :

SELECT * FROM user WHERE membergroupids IN (4) = 4 order by userid ASC
SELECT * FROM user WHERE membergroupids >= 4 order by userid ASC
SELECT * FROM user WHERE membergroupids IN ('4') = 4 order by userid ASC

Thank you in advance for your quick response.


nurv-xAsked:
Who is Participating?
 
dmitryz6Connect With a Mentor Commented:
SELECT * FROM user WHERE membergroupids like '%4%' order by userid
0
 
dmitryz6Commented:
use like

MS Access example

SELECT * FROM user WHERE membergroupids like *4*

sql server example

SELECT * FROM user WHERE membergroupids like %4%
0
 
dmitryz6Commented:
use like

MS Access example

SELECT * FROM user WHERE membergroupids like *4* order by userid

sql server example

SELECT * FROM user WHERE membergroupids like %4% order by userid
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
nurv-xAuthor Commented:
That only returned records in the table that only had a 4 and nothing else in the field.

I should also mention this is php code for connection with a MySQL database.
0
 
nurv-xAuthor Commented:
Thank you for your expert help! I was missing the ' ' around the percents!
0
 
dmitryz6Commented:
You welcome and good luck with your project
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.