How to Check if Something is an IP Address in MySQL

So I don't forget, this is a very useful bit of regex:

https://stackoverflow.com/questions/14699056/how-to-match-an-ip-address-in-mysql

SELECT *

FROM yourtable

WHERE 

  thecolumn REGEXP '^[0-9]{1,3}\\.[0-9]{1,3}\\.[0-9]{1,3}\\.[0-9]{1,3}$'

Very useful if you've got a list of server names, but among those server names are IP addresses which shouldn't really be there.

Also NOT REGEXP if you want to find that ones that aren't IP addresses.

Note: It will match invalid IP addresses like 999.999.999.999. If you only want to match valid IP addresses you'll need something more advanced.

Image: MySQL REGEXP to Match an IPv4 IP Address

For PowerShell

If you're injesting data into SQL and there's an easy way to transform the data before the data is injested into SQL, it makes sense to use that way. Mostly people work from Windows machines which usually allows access to PowerShell, so using PowerShell to transform a CSV before that CSV is imported into SQL, is a great option.

This link talks about:
PowerShell regex to accurately match IPv4 address (0-255 only)
https://www.powershelladmin.com/wiki/PowerShell_regex_to_accurately_match_IPv4_address_(0-255_only)

Basic regex if you don't care about having invalid IP addresses (like 999.999.999.999):
\b\d{1,3}\.d{1,3}\.d{1,3}\.\d{1,3}\b

The full version that does 0-255 only:
^(?:(?:0?0?\d|0?[1-9]\d|1\d\d|2[0-5][0-5]|2[0-4]\d)\.){3}(?:0?0?\d|0?[1-9]\d|1\d\d|2[0-5][0-5]|2[0-4]\d)$

Example of using it:
PS D:\> $Octet = '(?:0?0?[0-9]|0?[1-9][0-9]|1[0-9]{2}|2[0-5][0-5]|2[0-4][0-9])'
PS D:\> [regex] $IPv4Regex = "^(?:$Octet\.){3}$Octet$"
PS D:\> '1.10.100.0' -match $IPv4Regex