CODEWITHSUNDEEP
mysqlregex
Abby Chau Yu Hoi
Abby Chau Yu Hoi

Reputation: 1398

How to match an ip address in mysql?

For example, I am having a column storing data like this.

Apple
12.5.126.40
Smite
Abby
127.0.0.1
56.5.4.8
9876543210
Notes

How to select out only the rows with data in IP format?

I have tried with '^[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}$' but I have no idea why it also matches 9876543210

Upvotes: 13

Views: 20266

Answers (5)

kev
kev

Reputation: 1031

IS_IPV4() is a native mysql function that lets you check whether a value is a valid IP Version 4.

SELECT *
FROM ip_containing_table
WHERE IS_IPV4(ip_containing_column);

I don't have data, but I reckon that this must be the most solid and efficient way to do this.

There are also similar native functions that check for IP Version 6 etc.

Upvotes: 8

Mohammad M. Shahbazi
Mohammad M. Shahbazi

Reputation: 39

you could also use the useful function inet_aton()

SELECT *
FROM yourtable
WHERE inet_aton(thecolumn) is not null

Upvotes: 2

itlunch
itlunch

Reputation: 79

Lengthy but works fine:

mysql> SELECT '1.0.0.127' regexp '^([0-1]?[0-9]{1,2}|2[0-4][0-9]|25[0-5])\\.([0-1]?[0-9]{1,2}|2[0-4][0-9]|25[0-5])\\.([0-1]?[0-9]{1,2}|2[0-4][0-9]|25[0-5])\\.([0-1]?[0-9]{1,2}|2[0-4][0-9]|25[0-5])$';

Upvotes: 1

Michael Berkowski
Michael Berkowski

Reputation: 270767

You're going to need to use REGEXP to match the IP address dotted quad pattern.

SELECT *
FROM yourtable
WHERE 
  thecolumn REGEXP '^[0-9]{1,3}\\.[0-9]{1,3}\\.[0-9]{1,3}\\.[0-9]{1,3}$'

Technically, this will match values that are not valid IP addresses, like 999.999.999.999, but that may not be important. What is important, is fixing your data such that IP addresses are stored in their own column separate from whatever other data you have in here. It is almost always a bad idea to mix data types in one column.

mysql> SELECT '9876543210' REGEXP '^[0-9]{1,3}\\.[0-9]{1,3}\\.[0-9]{1,3}\\.[0-9]{1,3}$';
+---------------------------------------------------------------------------+
| '9876543210' REGEXP '^[0-9]{1,3}\\.[0-9]{1,3}\\.[0-9]{1,3}\\.[0-9]{1,3}$' |
+---------------------------------------------------------------------------+
|                                                                         0 |
+---------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT '987.654.321.0' REGEXP '^[0-9]{1,3}\\.[0-9]{1,3}\\.[0-9]{1,3}\\.[0-9]{1,3}$';
+------------------------------------------------------------------------------+
| '987.654.321.0' REGEXP '^[0-9]{1,3}\\.[0-9]{1,3}\\.[0-9]{1,3}\\.[0-9]{1,3}$' |
+------------------------------------------------------------------------------+
|                                                                            1 |
+------------------------------------------------------------------------------+

Another method is to attempt to convert the IP address to a long integer via MySQL's INET_ATON() function. An invalid address will return NULL.

This method is likely to be more efficient than the regular expression.

You may embed it in a WHERE condition like: WHERE INET_ATON(thecolumn) IS NOT NULL

SELECT INET_ATON('127.0.0.1');
+------------------------+
| INET_ATON('127.0.0.1') |
+------------------------+
|             2130706433 |
+------------------------+

SELECT INET_ATON('notes');
+--------------------+
| INET_ATON('notes') |
+--------------------+
|               NULL |
+--------------------+

SELECT INET_ATON('56.99.9999.44');
+----------------------------+
| INET_ATON('56.99.9999.44') |
+----------------------------+
|                       NULL |
+----------------------------+

Upvotes: 19

Explosion Pills
Explosion Pills

Reputation: 191799

This may not be the most efficient way, and it's not technically regex, but it should work:

SELECT col1 FROM t1 WHERE col1 LIKE '%.%.%.%';

Upvotes: 3

Related Questions