Mysql field name in 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, while others do not, there are entries in the table, such as 192.168.0 and 192.168.1.15

Now I have to see if this ip belongs to the bot. I thought something like that

 SELECT bot_name FROM bots WHERE __input_ip__ REGEXP '^ip_address' 

but this will not work for the obvious reason that it is looking for a line starting with ip_address.

So my question is: how to include the field name in sql regex?

Answer 1

You might want to consider storing the IP address as INT UNSIGNED. Also save 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 ask if the 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 general optimization. Even storing 8 bytes for an IP address and netmask makes up just over half of the CHAR storage (15). And bitwise operations are likely to be much faster than regular matching to an expression, and it is easier to avoid corner cases, for example, in a @Gumbo comment.

Answer 2

Try the following:

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

Answer 3

(This is a response to Andrew's answer, but does not match the comment.)

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

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

... with the exception of ANSI mode, it is not. Argh! To be compatible, you need to get a backslash in another way:

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

Ugh. It might be better to forget the regex and do this with string ops:

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