Backwards LIKE Statements

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.



Related Entries

9 people found this page useful, what do you think?

Trackbacks

Trackback Address: 619/020C2B6294B9B1FE6083C68823FDA2E9

Comments

On 01/10/2007 at 8:23:28 PM EST Matthew Lesko wrote:
1
A backward LIKE is subtly ingenious. Makes me jealous I never thought of it.

On 01/11/2007 at 3:17:13 AM EST Robin wrote:
2
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.

On 01/11/2007 at 7:25:36 AM EST Ben Nadel wrote:
3
Good stuff!

On 01/11/2007 at 8:19:26 AM EST David Stockton wrote:
4
Nice! I never thought of doing that but it will certainly come in useful in the future. Good point though Robin.

D

On 01/11/2007 at 7:15:51 PM EST Ronald Bradford wrote:
5
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

On 01/11/2007 at 9:04:44 PM EST Pete Freitag wrote:
6
Ronald - that's a handy function, thanks for pointing it out.

On 08/10/2007 at 8:50:46 AM EDT theking2 wrote:
7
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.

On 08/10/2007 at 8:53:31 AM EDT theking2 wrote:
8
... but as Robin has pointed out this is slow and actually only feasible for our nightly data warehouse load.

On 08/10/2007 at 9:34:50 AM EDT Robin wrote:
9
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].

On 08/10/2007 at 9:45:35 AM EDT Robin wrote:
10
@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.

On 08/10/2007 at 2:32:49 PM EDT theking2 wrote:
11
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... :-)

On 04/26/2008 at 4:44:14 PM EDT Rob L wrote:
12
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].

On 07/27/2009 at 2:21:50 PM EDT Joshua K Roberson wrote:
13
I've been using a reverse LIKE for a long time. I find that it doesn't work well if you want to match an IP to an IP/CIDR. Any thoughts on how to do that in SQL?

On 08/02/2010 at 9:48:15 AM EDT basis-vikna wrote:
14
????????! ?????????? ?????? 23% ?? ????? ???? ?? ?????????????????? ????, ?????, ??????? ?? ??????? ???????? ????? VEKA. 38 044 3325485, 2232407,

On 08/22/2010 at 9:32:40 AM EDT basis-vikna wrote:
15
????????! ?????????? ?????? 23% ?? ????? ???? ?? ?????????????????? ????, ?????, ??????? ?? ??????? ???????? ????? VEKA. 38 044 3325485, 2232407,

Post a Comment




  



Spell Checker by Foundeo

Recent Entries



foundeo


did you hack my cf?