Re: back references using regex

[Please copy the mailing list on replies so others can participate
in and learn from the discussion.]

On Wed, Sep 07, 2005 at 10:40:22PM -0700, Matthew Peter wrote:
> I did read the docs ;) I always do. The question I
> really wanted answered is how to reference the back
> references in my regular expressions parentheses. Like
> the 2nd position or 4th from a group. Like \2 or $2.
> Can I do this in postgres in the query?

Are you looking for something like this?

SELECT substring('abc.foo.foo.xyz' FROM '(([[:alpha:]]+)\\.\\2)');
substring
-----------
foo.foo
(1 row)

That is, one or more alphabetic characters followed by a dot followed
by the same set of characters (this is a simplistic example: it would
also match 'foo.oog' and return 'oo.oo').

Note that the back reference is \2 because it refers to the inner
set of parentheses (i.e., the subexpression with the second opening
parenthesis); the outer set is used here for capturing. And again,
note the escaped backslashes because we're using ordinary quotes.
With dollar quotes the above query would be:

SELECT substring('abc.foo.foo.xyz' FROM $$(([[:alpha:]]+)\.\2)$$);

--
Michael Fuhr