MySQL select based on regular expression

Question

In MySQL, I need to select all rows where a field is not an IP address (e.g. 12.32.243.43). Can this be done with MySQL only?

For example: I tried the following but it doesn't match.

select * from mytable where field not like '%.%.%.%'

Google search: "mysql regex" or "mysql regular expression". http://dev.mysql.com/doc/refman/5.1/en/regexp.html — Dan Grossman, Apr 29 '12 at 05:00

score 2 · Answer 1 · answered Apr 29 '12 at 05:03

2

Sure can. You would be looking for something like:

SELECT * FROM `table` WHERE NOT( some_field REGEXP '^[0-9+]\.[0-9]+\.[0-9+]\.[0-9+]')

answered Apr 29 '12 at 05:03

Menztrual

40,867
12
57
70

regexp cannot cover invalid ip address though: 12.32.243.299 – Michael Buen Apr 29 '12 at 05:26
1

@MichaelBuen: It *can*, but not very elegantly: `^(([01]\d{0,2}|2[0-4]?\d?|25[0-5]|[3-9]\d?)\.){3}([01]\d{0,2}|2[0-4]?\d?|25[0-5]|[3-9]\d?)$` – eggyal Apr 29 '12 at 11:10
Related: http://stackoverflow.com/questions/3655723/is-0-0-0-0-a-valid-ip-address – cmbuckley Apr 29 '12 at 15:04

score 2 · Answer 2 · answered Apr 30 '12 at 02:10

If using regular expression is not a requirement, this shall deliver the solution:

select stringBasedIp from x
where inet_aton(stringBasedIp) is null;


select stringBasedIp, (inet_aton(stringBasedIp) is null) as isInvalidIp
from x;

Sample data:

create table x(stringBasedIp varchar(16));

insert into x values
('255.255.255.255'),
('0.0.0.0'),
('0.0.0.300'),
('0.0.0.-1'),
('0.0.0.A'),
('192.168.0.1'),
('400.168.0.1'),
('12.32.243.43'),
('12.32.243.430');

Here are the list of invalid ip:

STRINGBASEDIP
0.0.0.300
0.0.0.-1
0.0.0.A
400.168.0.1
12.32.243.430

Live test: http://www.sqlfiddle.com/#!2/2a4ec/1

MySQL select based on regular expression

2 Answers2