Pete Freitag Pete Freitag

Backwards LIKE Statements

Published on January 10, 2007
By Pete Freitag
databases

Sometimes you need to think backwards.

Here was the problem. I needed to match up some IP address ranges to the company that owns them. Looking for a simple solution to the problem I came up with storing the IP address block patterns in the database as follows:

ip_pattern
----------------
127.%.%.%
192.168.%.%
10.%.%.%

Any idea why I choose % as the wildcard?

That's right - it's the wildcard operator in SQL for the LIKE statement.

So now when I have have an IP address 192.168.1.1, I can do what I like to call a backwards LIKE query:

SELECT company, ip_pattern
FROM company_blocks
WHERE '192.168.1.1' LIKE ip_pattern

This works on SQL Server and MySQL, and I would think it should work fine on any database server.



ip sql like mysql sqlserver

Backwards LIKE Statements was first published on January 10, 2007.

If you like reading about ip, sql, like, mysql, or sqlserver then you might also like:

Discuss / Follow me on Twitter ↯

Comments

A backward LIKE is subtly ingenious. Makes me jealous I never thought of it.
by Matthew Lesko on 01/10/2007 at 6:23:28 PM UTC
Nice article. However, keep in mind that with this technique no index will be used. Instead, MySQL needs to perform a table scan and try to match every single record against the given IP address. This is ok with few records, but if you'd have millions of rows, it becomes too slow.
by Robin on 01/11/2007 at 1:17:13 AM UTC
Good stuff!
by Ben Nadel on 01/11/2007 at 5:25:36 AM UTC
Ronald - that's a handy function, thanks for pointing it out.
by Pete Freitag on 01/11/2007 at 7:04:44 PM UTC
For all the telco's out there I've used exactly the same trick to do number-destination mapping. Got a table the starts off like:
DestinationName DialCode
USA 1
Canada 1204
Bahamas 1242
Barbados 1246
Barbados Mobile 124623

and with
SELECT TOP 1 @Destination = DestinationName
FROM [UM-SHARE].dbo.tblDestinationCode
WHERE RTRIM(LTRIM(@Number)) like DialCode + '%'
ORDER BY LEN(DialCode) DESC

one can find the destination (country) for a specific number.
by theking2 on 08/10/2007 at 6:50:46 AM UTC
... but as Robin has pointed out this is slow and actually only feasible for our nightly data warehouse load.
by theking2 on 08/10/2007 at 6:53:31 AM UTC
Bummer Robin. That would almost work. if not for the fact that world number plans are not nicely, consequetively structured. They are not. Take for instance 12462700000. With your query this would become a mobile number in Barbados, which it is not, it's a fixed number.
Ofcourse, once on Barbados you could't care less... :-)
by theking2 on 08/10/2007 at 12:32:49 PM UTC
Pete I'm a little late chiming in on this one but Ron's comment points to a much better solution. With the wildcards you've found a really neat trick but you need something more flexible to accommodate the full rang of subnets sizes. The answer is to store the IP and subnet mask in two INT() columns. In MySQL you can use the inet_ntoa() and inet_aton() functions when you need to convert to and from the standard dotted notation. If a someone only had half or part of the standard subned, lets say 0.0.1.0 through 0.0.1.127[so.. /25]. Now you can represent that in INT form. First store the network, 256. Then you can decide how to store the range. You store the ending ip of the range[383], a standard network mask[4294967168] or the as an inverted mask [127].
by Rob L on 04/26/2008 at 2:44:14 PM UTC