On Fri, 26 Jan 2007, Kohler, Andy wrote:
Ken Irwin asked:
I wonder if it's possible to use LIKE with
the results of a subquery, eg.:
SELECT * FROM table WHERE ip [NOT LIKE ANYTHING IN] (SELECT
ip_range FROM known_ips) where [NOT LIKE ANYTHING IN] is
probably some different wording.
In general, you'd do this like (hah):
SELECT *
FROM table t
WHERE NOT EXISTS
( SELECT *
FROM known_ips
WHERE ip = t.ip
)
I think what's Ken is asking for though is there some combination of the
IN operator and LIKE operator. He's trying to exclude a set of patterns,
ie converting (ip NOT LIKE "127.%" OR ip NOT LIKE "143.123.%").
Off the top of my head I can't think of anything like that (which isn't to
say much), but if by some stroke of luck you filter based on a certain
part of the address you could do a substring function. (You'd have to
check MySQL manual for the exact syntax)... Ie
SELECT *
FROM iptables
WHERE substring(iptables,1,7) NOT IN (127.475,...)
Jon Gorman
