BowerStudios.com

  • My Lab
  • Family
  • Friends
  • Professional
  • About
Home

Regular Expressions Example

daniel —Wed, 11/19/2008 - 9:23pm

  • Programming

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

Navigation

  • Search
  • Recent content
  • Contact Me
  • Mail
  • Pass Hasher
  • Bower Studios Admin

Quotes

daniel —Wed, 03/04/2009 - 8:33am

We must not let our rulers load us with perpetual debt. We must make our election between economy and liberty or profusion and servitude. If we run into such debt, as that we must be taxed in our meat and in our drink, in our necessaries and our comforts, in our labors and our amusements, for our calling and our creeds...[we will] have no time to think, no means of calling our miss-managers to account but be glad to obtain subsistence by hiring ourselves to rivet their chains on the necks of our fellow-sufferers... And this is the tendency of all human governments. A departure from principle in one instance becomes a precedent for[ another]... till the bulk of society is reduced to be mere automatons of misery... And the fore-horse of this frightful team is public debt. Taxation follows that, and in its train wretchedness and oppression.

—

Thomas Jefferson

  • Log in to post comments
  • daniel's quotes

Popular content

Last viewed:

  • Software used in this walkthrough
  • Grails Sanitizer updated and released for Grails 2.2, 2.3 & 2.4
  • Interesting CPU Tables
  • Thomas A. Edison
  • Never underestimate the power of pretty.

Copyright 2021 Daniel Bower
  • My Lab
  • Family
  • Friends
  • Professional
  • About