• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 185
  • Last Modified:

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

  • 2
1 Solution
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.

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now