How to match an ip address in mysql?

Question

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

Answer 1

you could also use the useful function inet_aton()

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

Answer 2

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 '%.%.%.%';

Answer 3

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 |
+----------------------------+

Answer 4

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.

Answer 5

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])$';