Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 278
  • Last Modified:

Using LIKE with IN

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
metalaureate
Asked:
metalaureate
2 Solutions
 
arbertCommented:
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
 
metalaureateAuthor Commented:
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
 
jchopdeCommented:
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
 
appariCommented:
try something like this

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

0
 
metalaureateAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

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