Backwards LIKE Statements
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
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.
- INFORMATION_SCHEMA Support in MySQL, PostgreSQL - February 18, 2008
- SQL to Select a random row from a database table - September 14, 2005
- SQL Reserved Key Words Checker Tool - March 28, 2005
- Top 10 Reserved SQL Keywords - October 28, 2008
- SQL Case Statement - October 15, 2008
- Updating Java on ColdFusion or Lucee
- ColdFusion returning empty response with server-error: true
- Careful applying CF11u16, CF2016u8, CF2018u2
- Sessions don't work in Chrome but do in IE
- csrfVerifyToken does not invalidate the token
- The cf_sql_ is optional in cfqueryparam
- Cookie Expires / Max-Age 1969-12-31T23:59:59.000Z
- Burst Throttling on AWS API Gateway Explained