I need to search a list in a field

I have a field that contains a numeric list (1,2,4,12,13,15)

I want to pull all records that contain the item '2'.  However doing a search for :: like * & '2' & * ::  returns a record where the field contains 12 and not 2.

If I change it to read ::  like *& ',2,' &* ::  I get only items containing '2'  so no items containing only 2 or 2,3  or 1,2

any ideas on how I can do this in a query?  I am not using vbscript at all - just a simple query.
FROM tbl_Members
WHERE member_List Like "*" & [which list] & "*"

Open in new window

Who is Participating?
SQLSharkConnect With a Mentor Commented:
To begin with, 125 points is not nearly enough for this question.  Do these points cost you real money?

Second, you should not have a FIELD in SQL which contains a Numeric list.  That isn't just BAD table design, it is HORRIBLE table design.  A FIELD should contain a Number, not a List.  Read this topic:


However, I understand that you probably can't change your awful database now.  I feel your pain.  Here is a query to return the data you want:

FROM tbl_Members
WHERE member_List = '2'                         --Member list has only one member
        OR LEFT(member_List,2) = '2,'         --Member list begins with the member 2
        OR RIGHT(member_List,2) = ',2'       --Member list ends with the member 2
        OR member_List LIKE '%,2,%'          --The member occurs in the middle of the list

Note:  this query will only work if your list really is comma delimited and only if it does not contain SPACES.  This kind of complexity is just one reason you don't design databases in this way.  The performance on this query will also be relatively bad,  because you are doing so much string parsing.

Give me the points, my friend.  You won't get any replies from anyone else for 125 points.
jtheriauAuthor Commented:
I was thinking - maybe a simple regEx to find  2,  or ,2  or ,2,  Any ideas?
Well, '2,' could be a part of '12,' and ',2' could be a part of ',22' and that is not what you want.

This is the kind of trouble you run into with fields that contain delimited lists.
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.