Mysql field name within regular expression

Question

I have the following table

Table bots{
    ip_address varchar(15),
    bot_name varchar(32)
}

Given that some bots have static ips and others won't, the table has entries such as 192.168.0 and 192.168.1.15

Now I have to see if a given ip belongs to a bot. I was thinking something along these lines

SELECT bot_name
FROM bots
WHERE __input_ip__  REGEXP '^ip_address'

but this won't work for the obvious reason that it is looking for a string that starts with ip_address.

So my question is, how can I include a field name within a sql regular expression ?

Answer 1

You might want to consider storing the IP address as an INT UNSIGNED. Also store the netmask so you can tell the difference between a static address and a subnet.

INSERT INTO bots (ipaddress, netmask, bot_name) 
VALUES (INET_ATOI('192.168.1.0'), INET_ATOI('255.255.255.0'), 'Wall-E');

Then you can query to see if an input IP address matches:

SELECT bot_name
FROM bots
WHERE __input_ip__ & netmask = ipaddress & netmask;

Using integers for IP addresses instead of CHAR(15) is a common optimization. Even storing the 8 bytes for the IP address and the netmask is little more than half the storage of the CHAR(15). And the bitwise operations are likely to be a lot faster than the regular expression matching, and it's easier to avoid the corner cases like in @Gumbo's comment.

Answer 2

Try this:

SELECT bot_name
FROM bots
WHERE __input_ip__  REGEXP concat('^', replace(ip_address, '.', '\.'))

Answer 3

(This is a response to Andrew's answer but doesn't fit in a comment.)

WHERE __input_ip__  REGEXP concat('^', replace(ip_address, '.', '\.'))

Good plan, except that in MySQL \ is a (non-standard SQL) string literal escape, so to get one in the regexp you'd probably need '\\\.'!

...except in ANSI mode it wouldn't. Argh! To be compatible you'd have to get the backslash another way:

WHERE __input_ip__ REGEXP CONCAT('^', REPLACE(ip_address, '.', CHAR(92, 46)))

Ugh. Maybe better to forget regex and do it with string ops:

WHERE LEFT(__input_ip__, CHAR_LENGTH(ip_address))=__input_ip__