Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Mysql field name within regular expression

Tags:

regex

sql

mysql

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 ?

like image 950
Scott Avatar asked Sep 22 '09 15:09

Scott


3 Answers

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.

like image 109
Bill Karwin Avatar answered Sep 28 '22 14:09

Bill Karwin


Try this:

SELECT bot_name
FROM bots
WHERE __input_ip__  REGEXP concat('^', replace(ip_address, '.', '\.'))
like image 38
Andrew Hare Avatar answered Sep 28 '22 15:09

Andrew Hare


(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__
like image 26
bobince Avatar answered Sep 28 '22 14:09

bobince