[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SQL Like with Multiple Values

Posted on 2014-08-22
4
Medium Priority
?
357 Views
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.
0
Comment
Question by:holemania
4 Comments
 
LVL 53

Expert Comment

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

Expert Comment

by:Randy Poole
ID: 40279082
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

0
 
LVL 14

Accepted Solution

by:
Russell Fox earned 2000 total points
ID: 40279255
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 
	('ABC'),
	('DDD'),
	('123GAS')

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

0
 

Author Closing Comment

by:holemania
ID: 40315742
Thank you.
0

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

829 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