I need to search a list in a field

Posted on 2008-11-14
Last Modified: 2012-05-05
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

Question by:jtheriau
    LVL 2

    Accepted 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:

    SELECT *
    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.

    Author Comment

    I was thinking - maybe a simple regEx to find  2,  or ,2  or ,2,  Any ideas?
    LVL 2

    Expert Comment

    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.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Suggested Solutions

    Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
    This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
    Sending a Secure fax is easy with eFax Corporate ( First, Just open a new email message.  In the To field, type your recipient's fax number You can even send a secure international fax — just include t…
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    10 Experts available now in Live!

    Get 1:1 Help Now