regex - MYSQL REGEXP INET_NTOA IP ADDRESS SEARCHING -
i'm having trouble understanding why following results mysql query of ip addresses. i'm storing ip addresses integers.
mysql> select ip ip ipaddress; +-----------+ | ip | +-----------+ | 168456058 | | 168456059 | | 168456060 | | 168456061 | | 168456317 | | 168456570 | | 168489683 | | 168489583 | | 168489683 | +-----------+ 9 rows in set (0.00 sec)
here how data looks when converting integers ip address.
mysql> select inet_ntoa(ip) ip 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 search particular subnet, such 111, results expect:
mysql> select inet_ntoa(ip) ip ipaddress 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 use decimal in query, no results.
mysql> select inet_ntoa(ip) ip ipaddress inet_ntoa(ip) regexp '[[:<:]]\.111[[:>:]]'; empty set (0.00 sec)
the decimal in query works if change search query .11. time, though, of .111, and .211.
mysql> select inet_ntoa(ip) ip ipaddress 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 .11 search work , .111 not work and, when use .11 search, .211 results well?
your regular expression self-contradictory.
you bounding pattern on word boundaries, , including non-word character in string you're looking for. since '.' character isn't word character, there never strings delimited word boundaries include '.'.
put way, since '.' word boundary, looking [word-boundary][word-boundary][word], cannot occur.
just take out word boundaries in regexp:
where inet_ntoa(ip) regexp '\.111'
Comments
Post a Comment