Scrubbing IPv4 dotted-quad addresses in SQL

Given a table containing dotted quad IPv4 addresses stored as a VARCHAR(15), for example:


what's a convenient way to SELECT all "ipv4" fields with the final two octets scrubbed, so that the above would become:


Target RDBMS is postgresql 8.4, but the more portable the better!


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


For postgres:

select regexp_replace('', 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 '' from my_ip_table;

Manual reference:

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


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)