How to Select Invalid Database IP Text Field

2010-06-08

Normally you would validate an IP address before you store it in a database. I had a case where I needed to filter out invalid IP's stored in a PostgreSQL database and finally settled on this solution.

select * from myIPtable
where cast(split_part(client_ip,'.',1) as int) > 254
    or cast(split_part(client_ip,'.',1) as int) < 0
    or cast(split_part(client_ip,'.',2) as int) > 254
    or cast(split_part(client_ip,'.',2) as int) < 0
    or cast(split_part(client_ip,'.',3) as int) > 254
    or cast(split_part(client_ip,'.',3) as int) < 0
    or cast(split_part(client_ip,'.',4) as int) > 254
    or cast(split_part(client_ip,'.',4) as int) < 0

It is a little more complicated if you are stuck with MySQL as your database.

http://www.kanolife.com/escape/2006/03/mysql-string-splitter.html