RE: IP regex

SELECT columnName FROM tableName WHERE columnName REGEXP
'^[0-9]{3}[.][0-9]{3}[.][0-9]{2}[.][0-9]{2}$';

Explanation:

Substitute columnName with the name of the field holding the IP Address
Substitute tableName with the name of the table holding the IP Addresses.

REGEXP - means use regular expression

^			means start matching at the beginning of the line.
[0-9]		means match any number 0-9
{3}|{2}	means match 3|2 instances of the preceding element
[.]			means match the . character as opposed to the
regular expression meaning of . - which is any character
			This prevents something like 1234567890123 from
being accepted as valid.
			The 4th, 8th and 11th characters must be a .
$			means stop matching anything else.

The above REGEXP matches 123.123.12.12 but not 1234123812912.

Basically, 
Starting at the front of the value match:
3 digits 
followed by a period 
followed by 3 digits 
followed by a period 
followed by 2 digits 
followed by a period 
followed by 2 digits
Ends there.

mysql> select '1231123112112' regexp
"^[0-9]{3}[.][0-9]{3}[.][0-9]{2}[.][0-9]{2}";
+---------------------------------------------------------------------+
| '1231123112112' regexp "^[0-9]{3}[.][0-9]{3}[.][0-9]{2}[.][0-9]{2}" |
+---------------------------------------------------------------------+
|                                                                   0 |
+---------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select '123.123.12.12' regexp
"^[0-9]{3}[.][0-9]{3}[.][0-9]{2}[.][0-9]{2}";
+---------------------------------------------------------------------+
| '123.123.12.12' regexp "^[0-9]{3}[.][0-9]{3}[.][0-9]{2}[.][0-9]{2}" |
+---------------------------------------------------------------------+
|                                                                   1 |
+---------------------------------------------------------------------+
1 row in set (0.00 sec)

-Gui


-----Original Message-----
From: Greg Meckes [mailto:[email protected] <mailto:[email protected]>
] 
Sent: Saturday, October 30, 2004 11:53 PM
To: Gregg R.Allen; [email protected]
Subject: Re: IP regex


Here's a quick and dirt way:

use strict;

my $ip = '0.0.0.0.0';

my @ips = split(/\./,$ip);

my $classes = @ips;

if ($classes != 5) {
print "IP not valid\n";
}
    else {
    print "IP Valid\n";
    }

Greg

--- "Gregg R.Allen" <[email protected]> wrote:

> I'm sure this is very simple, I just can't seem to learn Regexes:
> 
> I have been given the chore of taking a DB table of client records,
> which has a field called IP address.  Whenever a client logs onto our 
> site, I'm supposed to display the IP address they logged on from THE 
> LAST TIME they visited, as a security measure.
> 
> This field is a MySQl varchar(30). Since this field was added at table
> creation, but not used until recently, many of the fields contain NULLs 
> or junk text.
> 
> Can someone point me in the direction of creating REGEX to test 
> whether
> an IP address is valid. (Valid in the sense of syntax i.e. 
> 192.168.24.20 is  valid, but
> 277.22.49.75, or 65.23 is not)?
> 
> Thanks in advance
> 
> Gregg Allen
> 
> 
> --
> MySQL Perl Mailing List
> For list archives: http://lists.mysql.com/perl
<http://lists.mysql.com/perl> 
> To unsubscribe:    http://lists.mysql.com/[email protected]
<http://lists.mysql.com/[email protected]> 
> 
> 



		
__________________________________
Do you Yahoo!?
Take Yahoo! Mail with you! Get it on your mobile phone.
http://mobile.yahoo.com/maildemo <http://mobile.yahoo.com/maildemo>  

-- 
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl <http://lists.mysql.com/perl>

To unsubscribe:
http://lists.mysql.com/[email protected]
<http://lists.mysql.com/[email protected]>