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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.