SQL Like with Multiple Values

Posted on 2014-08-22
Last Modified: 2014-09-10
I need to add a filter with multiple "Not LIke" in my query.  Similar to how you do it to like the "In" query.

Select *
From  Item_Master
Where Item_ID In ('ABC', 'DDD', '123GAS')

Issue is that the string for not like can be one or many and dynamic.  So I do not want to defy multiple "Not Like" in my query.
Question by:holemania
    LVL 44

    Expert Comment

    by:Vitor Montalvão
    If you have a dynamic criteria you can only achieve this with a dynamic query (construct the query in runtime).
    LVL 21

    Expert Comment

    by:Randy Poole
    You will need to append and build the filter using or/and clauses
    Select * from item_master where  not  (item_ID like 'AB%' or item_ID like 'DD%')

    Open in new window

    LVL 13

    Accepted Solution

    Dynamic SQL is the most common solution, generating a string query and using sp_executesql to run it. You can also try something like this:
    DECLARE @NotLike TABLE (NotLike VARCHAR(25))
    --	Dynamically insert your terms:
    INSERT INTO @NotLike (NotLike) VALUES 
    Select *
    From  Item_Master t1
    	LEFT JOIN @NotLike t2
    		-- Strange, but this works:
    		ON t1.Item_ID LIKE '%' + t2.NotLike + '%'
    -- Exclude ones where there IS a match:
    WHERE t2.NotLike IS NULL

    Open in new window


    Author Closing Comment

    Thank you.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
    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.
    In this sixth video of the Xpdf series, we discuss and demonstrate the PDFtoPNG utility, which converts a multi-page PDF file to separate color, grayscale, or monochrome PNG files, creating one PNG file for each page in the PDF. It does this via a c…
    This video discusses moving either the default database or any database to a new volume.

    759 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