Solved

Using LIKE with IN

Posted on 2004-04-26
5
272 Views
Last Modified: 2009-07-29
Experts,

I have a query of the form

Select Field1 from T1
where Field1 not in
(Select Excluded_Values from T2)

I want the T2 table values to be wildcard-matched against Field1 (i.e. for the IN match to be a LIKE). LIKE creates an error. Anyone know how I do this?
0
Comment
Question by:metalaureate
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 34

Expert Comment

by:arbert
ID: 10924986
I guess we need a little more to go on....Like, like you said is usually used with a wild card--how many characters do you want to match????  
0
 

Author Comment

by:metalaureate
ID: 10925002
I have  list of IP addresses to exclude, i.e.

66.180.5.%
128.1.4.%

etc.

I want to exclude all records  that match these IP masks.

The problem arose when my list of excluded IP addresses grew too large for concaternations of (AND NOT LIKE '66.180.5.%') etc. etc.
0
 
LVL 6

Assisted Solution

by:jchopde
jchopde earned 200 total points
ID: 10925119
you could probably do something like

SELECT T1.Field1 FROM T1 WHERE T1.Field1 NOT IN (SELECT T1.Field1 FROM T1,T2 WHERE T1.Field1 LIKE '%' + T2.Excluded_Values)

If Excluded_Values will not contain the '%' in the database, you would add that to the end of the query.
0
 
LVL 39

Accepted Solution

by:
appari earned 300 total points
ID: 10925164
try something like this

Select Field1 from T1
where not exists
(Select Excluded_Values from T2 where Field1 like Excluded_Values )

0
 

Author Comment

by:metalaureate
ID: 10925276
Thanks. You got it.

Both jchopde and appari's answer achieve the same results, but jchopde's solution is 600% faster. Anyone know why?
0

Featured Post

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

623 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