pf » Backwards LIKE Statements

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

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

A backward LIKE is subtly ingenious. Makes me jealous I never thought of it.

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.

Good stuff!

Nice! I never thought of doing that but it will certainly come in useful in the future. Good point though Robin.

D

I recommend you check out the INET_ATON() function, will make your life a lot easier and efficient in many ways.

http://dev.mysql.com/doc/refman/5.1/en/miscellaneous-functions.html

Ronald http://blog.arabx.com.au

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.

... but as Robin has pointed out this is slow and actually only feasible for our nightly data warehouse load.

Here is a method that performs quite well:

# 127.%.%.% # 192.168.%.% # 10.%.%.%

INSERT INTO company_blocks (company, ip_from, ip_to) VALUES(INET_ATON('127.0.0.1'), INET_ATON('127.255.255.255')), (INET_ATON('192.168.0.0'), INET_ATON('192.168.255.255')), (INET_ATON('10.0.0.0'), INET_ATON(10.255.255.255'));

SELECT company, INET_NTOA(ip_from), INET_NTOA(ip_to) FROM company_blocks WHERE ip_from <= INET_ATON('192.168.1.1') AND ip_to >= INET_ATON('192.168.1.1') LIMIT 1;

(Haven't tested this but) this should make efficient use of a multi column index [ip_from, ip_to].

@theking2: Unfortunately I can't test this on MSSQL but it works for MySQL: This gives you the same result faster by using an index:

SELECT TOP 1 @Destination = DestinationName FROM [UM-SHARE].dbo.tblDestinationCode WHERE DialCode <= RTRIM(LTRIM(@Number)) ORDER BY DialCode DESC;

DialCode must be a text field or your rdbms must do automatic type casting.

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... :-)

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].