Beefy Boxes and Bandwidth Generously Provided by pair Networks
"be consistent"
 
PerlMonks  

(OT) mysql regexp unwanted matches for specific IP

by kp2a (Sexton)
on Jan 30, 2013 at 12:01 UTC ( #1016045=perlquestion: print w/ replies, xml ) Need Help??
kp2a has asked for the wisdom of the Perl Monks concerning the following question:

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?

Comment on (OT) mysql regexp unwanted matches for specific IP
Select or Download Code
Re: (OT) mysql regexp unwanted matches for specific IP
by McDarren (Abbot) on Jan 30, 2013 at 12:07 UTC

    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 ;-)

      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.

      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?
Re: (OT) mysql regexp unwanted matches for specific IP
by keszler (Priest) on Jan 30, 2013 at 15:04 UTC
    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?
Re: (OT) mysql regexp unwanted matches for specific IP
by choroba (Abbot) on Jan 30, 2013 at 15:45 UTC
    . 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.
    لսႽ ᥲᥒ⚪⟊Ⴙᘓᖇ Ꮅᘓᖇ⎱ Ⴙᥲ𝇋ƙᘓᖇ

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://1016045]
Approved by sundialsvc4
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others meditating upon the Monastery: (8)
As of 2014-07-30 01:58 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    My favorite superfluous repetitious redundant duplicative phrase is:









    Results (229 votes), past polls