(OT) mysql regexp unwanted matches for specific IP

[kp2a]

task: seek record that contains a specific IP --- I have tried:

select * from table where field regexp '^10.100.1.1'
select * from table where field regexp '10\.100\.1\.1'
select * from table where field regexp '10\.100\.1\.1[[:>:]]'
select * from table where field regexp '[[:<:]]10\.100\.1\.1[[:>:]]'

and so on . . . all same result - many matches where even the first six character do not match - help!

mysql> select d07 from service where d07 regexp '^10.100.1.1';
+------------------------------------------------+
| d07                                            |
+------------------------------------------------+
| 10.100.101.186 on at 2011-06-20 02:12:02       |
| 10.100.101.188 at 2009-05-12 05:12:47          |
| 10.100.101.251 at 2009-03-26 16:00:37          |
| 10.100.101.182 at 10282300                     |
| 10.100.101.70 on at 2011-04-25 02:16:27        |
| 10.100.101.59 off since 2012-02-18 18:52:04    |

. . .

here is the behavior I expected: mysql> select d07 from service where d07 regexp '10.100.101.95'; +-----------------------------------------+ | d07 | +-----------------------------------------+ | 10.100.101.95 on at 2011-05-24 02:08:49 | | 10.100.101.95 on at 2010-04-24 15:00:04 | +-----------------------------------------+ 2 rows in set (0.05 sec) could it be that there is something magic about 10.100.1.1?

[McDarren]

If you're looking for a specific IP address, then why bother messsing around with a pattern match?

I mean, what's wrong with:

select * from table where field like '%10.100.1.1%';

P.S. This is a Perl site, not a MySQL site ;-)

[hippo]

Almost there, but to match the OP's criteria you'll need an extra whitespace, so assuming the address is the first string in the field:

select * from table where field like '10.100.1.1 %';

It still has nothing to do with Perl, though.

[kp2a]

because

mysql> select state,d09,d07 from service where d07 like '%10.100.1.1%'
+;
+-------+--------------------------+--------------+
| state | d09                      | d07          |
+-------+--------------------------+--------------+
|     0 | cr1.pullpnt 25512        | 10.100.1.158 |
|     0 | Link PP2Prn 25506        | 10.100.1.157 |
|     0 | Link PP2Sol 25505        | 10.100.1.130 |
|     0 | CV2SOL 26754             | 10.100.1.138 |
|     0 | Link Sol2CV 26775        | 10.100.1.139 |
|     0 | Link Sol2PP 27014        | 10.100.1.131 |
|     0 | Link Princess2Pull 27016 | 10.100.1.154 |
|     0 | br1.glbx 28281           | 10.100.1.1   |
+-------+--------------------------+--------------+
8 rows in set (0.02 sec)

but I tried - thanks suggestion -

mysql> select state,d09,d07 from service where d07 like '%10.100.1.1';
+-------+----------------+------------+
| state | d09            | d07        |
+-------+----------------+------------+
|     0 | br1.glbx 28281 | 10.100.1.1 |
+-------+----------------+------------+
1 row in set (0.02 sec)

yes, I know perl site - a lot of wise monks - and regexp experts - tried mysql sites no help now you regexp experts, why the unexpected regexp behavior? is there something to learn here other than when to use regexp v. like?

[choroba]

. has a special meaning in regexps. Therefore, ^10.100.1.1 in fact means From the beginning, 1, 0, anything, 1, 0, 0, anything, 1, anything, 1.
10.100.101 matches your regexp: the last 0 corresponds to the last dot.

[keszler]

task: seek record that contains a specific IP
Why not just select * from table where field = '10.100.1.1'? If you want one specific, known IP why use regexp or like?