At Collaborate, and on the Web, come on and join us and F2F with the tech-saavy! COLLABORATE Social Media Hour  Tues, 4/24, 1–2 pm Exhibit Hall-IOUG Booth There’s no 140 character limit for this meet up! Stop by the IOUG … Continue reading →

A friend of mine asked me: I’m hoping you can help me out with something — I’m trying to optimize a search feature. Since it uses a MySQL database, the search already uses the LIKE statement to get matches for a search query, we might be needing something more flexible. I found mention on MySQL’s website [...]

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.