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

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.