Scrubbing IPv4 dotted-quad addresses in SQL
- by pilcrow
Given a table containing dotted quad IPv4 addresses stored as a VARCHAR(15), for example:
ipv4
--------------
172.16.1.100
172.16.50.5
172.30.29.28
what's a convenient way to SELECT all "ipv4" fields with the final two octets scrubbed, so that the above would become:
ipv4
------------
172.16.x.y
172.16.x.y
172.30.x.y
Target RDBMS is postgresql 8.4, but the more portable the better!
Thanks.