Scrubbing IPv4 dotted-quad addresses in SQL

Question

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.

UPDATE: while I do appreciate (and do upvote) slick INET/CIDR answers, I am looking to produce a string output with non-numeric characters substituted for the final two octets. (And, again, the more portable the better!)

Answer 1

For postgres:

select regexp_replace('172.16.1.100', E'(.\\d+){2}$', '.x.y');

Answer 2

Conventional is to convert column type to inet/cidr

EDIT: With this native data type there's quite a few specific functions that perform much better than any string manipulation.

Answer 3

If you use the Postgres inet type, then you can do this with the inet operators, eg. <<= means 'is contained within'. I suspect that something lik the following will do what you need:

select my_ipaddress & inet '255.255.0.0' from my_ip_table;

Manual reference: http://www.postgresql.org/docs/8.4/static/functions-net.html