Regular Expressions Example
daniel Wed, 11/19/2008 - 9:23pm
From: http://www.issociate.de/board/post/131301/IP_regex.html
SELECT columnName FROM tableName WHERE columnName REGEXP
'^[0-9]{3}[.][0-9]{3}[.][0-9]{2}[.][0-9]{2}$';
Explanation:
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.
I don't believe a regular expression is the right way to go here. (Note that neither of the 2 regex solutions previously given do what you want -- they would both accept 999.999.00.00 as valid, for example.) You could write a horribly complicated regular expression to do what you want, but it would be hard to make sure you got it right. You'd also be reinventing the wheel, as there are already functions designed to work with IPs.
Spenser is on the right track, but INET_ATON() does not always return NULL for invalid IPs. Using Gregg's other example, INET_ATON('65.23') returns 1090519063, which is really 65.0.0.23. The trick is to use both INET_ATON() and INET_NTOA(), because ip = INET_NTOA(INET_ATON(ip)) only if ip is valid.
My advice: It makes no sense to keep IPs in a VARCHAR(30) column. (Even if a string was the right way to go, you'd need at most 15 characters.) The better way is to use an INT UNSIGNED. Then you store an IP using INET_ATON(IP) and retrieve it with INET_NTOA(ip_column). That takes 4 bytes instead of 8 to 16, and reduces the likelihood of garbage values in the column. Thus, I'd suggest
ALTER TABLE clients ADD new_ip INT UNSIGNED;
UPDATE clients SET new_ip = INET_ATON(old_ip)
WHERE old_ip = INET_NTOA(INET_ATON(old_ip));
Sample results:
SELECT old_ip, INET_NTOA(new_ip) AS ip FROM clients;
+-----------------+-----------------+
| old_ip | ip |
+-----------------+-----------------+
| 192.168.24.20 | 192.168.24.20 |
| 255.255.255.255 | 255.255.255.255 |
| 65.23 | NULL |
| 277.22.49.75 | NULL |
| junk text | NULL |
| 192.168.0.7 | 192.168.0.7 |
+-----------------+-----------------+
6 rows in set (0.00 sec)
Then you could drop column old_ip and rename new_ip to old_ip. (Of course,
you'd change "clients" and "old_ip" to fit your actual table.) Going
forward, you should modify your apps to only store valid IPs in the first place.
- Log in to post comments