Scrubbing IPv4 dotted-quad addresses in SQL

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!)

Answers


For postgres:

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

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


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.


Need Your Help

Sending array data from one view controller to another

swift

I am trying to copy an array from one view controller to another view controller, but somehow it does not work.. I have tried using this code:

os.listdir etc fails on shared windows path (Python 2.5)

python path

I am seeing some weird behavior while parsing shared paths (shared paths on server, e.g. \storage\Builds)