• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 276
  • 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

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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