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

Popular posts from this blog

java.util.scanner - How to read and add only numbers to array from a text file -

rewrite - Trouble with Wordpress multiple custom querystrings -

php - Accessing static methods using newly created $obj or using class Name -