Regular expression for extracting an IP address from a string in PostgreSQL

Question

I am dealing with a table called ipaddr with a column called destination and have entries in the column that look like and are of type varchar

tunnel://169.96.88.11:80/
url://169.96.88.30
169.96.88.59:443

These are all possibilities. I want to write a regex statement that returns true when it just matches the first three octets of the IP and nothing more. So, all three examples above match 169.96.88.

How do you write a regular expression so that I select rows in the table based on a specific ip address subnet?

select * from ipaddr where destination like '%169.96.88%'

That is, I am tying to collect all records that have a destination entry in the 169.96.88.* block.

Answer 1

SELECT *
FROM   ipaddr 
WHERE  destination ~ '(://|^)169\.96\.88\.[0-9]';

The pattern starts at the beginning of the string or with ://.
Then follows the network, a dot and at least one more digit.

Tested with PostgreSQL 9.1.4. Note that I use the now default standard_conforming_strings. Else you have to write:

WHERE  destination ~ E'(://|^)169\\.96\\.88\\.[0-9]';