CODEWITHSUNDEEP
mysqlregexip-address
Marc
Marc

Reputation: 1760

MYSQL REGEXP INET_NTOA IP ADDRESS SEARCHING

I'm having trouble understanding why I get the following results from a MySQL query of IP addresses. I'm storing IP addresses as INTEGERS.

mysql> SELECT ip AS ip FROM ipaddress;
+-----------+
| ip        |
+-----------+
| 168456058 |
| 168456059 |
| 168456060 |
| 168456061 |
| 168456317 |
| 168456570 |
| 168489683 |
| 168489583 |
| 168489683 |
+-----------+
9 rows in set (0.00 sec)

Here is how the data looks when converting the INTEGERS to an IP address.

mysql> SELECT INET_NTOA(ip) AS ip FROM ipaddress;
+---------------+
| ip            |
+---------------+
| 10.10.111.122 |
| 10.10.111.123 |
| 10.10.111.124 |
| 10.10.111.125 |
| 10.10.112.125 |
| 10.10.113.122 |
| 10.10.242.211 |
| 10.10.242.111 |
| 10.10.242.211 |
+---------------+
9 rows in set (0.00 sec)

When I search for a particular subnet, such as 111, I get the results I expect:

mysql> SELECT INET_NTOA(ip) AS ip FROM ipaddress WHERE INET_NTOA(ip) REGEXP '[[:<:]]111[[:>:]]';
+---------------+
| ip            |
+---------------+
| 10.10.111.122 |
| 10.10.111.123 |
| 10.10.111.124 |
| 10.10.111.125 |
| 10.10.242.111 |
+---------------+
5 rows in set (0.00 sec)

If I use a decimal in the query, I get no results.

mysql> SELECT INET_NTOA(ip) AS ip FROM ipaddress WHERE INET_NTOA(ip) REGEXP '[[:<:]]\.111[[:>:]]';
Empty set (0.00 sec)

The decimal in the query works if I change the search query to .11. This time, though, I get all of the .111, and the .211. 

mysql> SELECT INET_NTOA(ip) AS ip FROM ipaddress WHERE INET_NTOA(ip) REGEXP '[[:<:]]\.11[[:>:]]';
+---------------+
| ip            |
+---------------+
| 10.10.111.122 |
| 10.10.111.123 |
| 10.10.111.124 |
| 10.10.111.125 |
| 10.10.242.211 |
| 10.10.242.111 |
| 10.10.242.211 |
+---------------+
7 rows in set (0.00 sec)

Why would the .11 search work and .111 not work and, when I use .11 to search, I get the .211 results as well?

Upvotes: 1

Views: 884

Answers (2)

AdrianBR
AdrianBR

Reputation: 2588

the [[:>:]] is used for words and separates using alphanumeric characters or underscores. Dot is not alphanumeric. you are loking for a separated word in a string where the word is not separated (dot is part of the word). just use WHERE INET_NTOA(ip) like "%.111"

Upvotes: 1

D Mac
D Mac

Reputation: 3809

Your regular expression is self-contradictory.

You are bounding your pattern on word boundaries, and then including a non-word character in the string you're looking for. Since the '.' character isn't a word character, there will never be any strings that are delimited by word boundaries that include the '.'.

Put another way, since the '.' is itself a word boundary, you are looking for [word-boundary][word-boundary][word], which cannot occur.

Just take out the word boundaries in your REGEXP:

WHERE INET_NTOA(ip) REGEXP '\.111'

Upvotes: 1

Related Questions