Message-ID: <4A03C276.9050007@postnewspapers.com.au>
Date: Fri, 08 May 2009 13:26:14 +0800
From: Craig Ringer <craig@postnewspapers.com.au>
User-Agent: Thunderbird 2.0.0.21 (X11/20090409)
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: select regexp_matches('a a a', '([a-z]) a','g');
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

First: Please don't reply to an existing message to create a new thread.
Your mail client copies the replied-to message ID into the References:
header, and well-implemented mail clients will thread your message under
a now-unrelated thread.

Compose a new message instead.

Marc Mamin wrote:

> I have a string that contains a serie of chars, separated by single
> spaces.
>
> e.g 'a b x n r a b c b'
>
> Having such a string, I d'like to get a list of all predecessors of a
> given character.
> In the example, the predecessors of b  are a,a,c.

OK, so wherever `b' occurs, you want the character at index `b -2'.

> select regexp_matches('a a a', '([a-z]) a','g');
> => {"a "} only

The issue is that regular expressions don't like to overlap matches. The
first match consumes _two_ leading `a' characters.

What you need is a zero-width lookahead assertion, available in
Perl-style extended regular expressions.  Handily, recent PostgreSQL
versions support these, so you can write:

test=> select regexp_matches( 'a a a', '([a-z]) (?=a)', 'g');
 regexp_matches
----------------
 {a}
 {a}
(2 rows)

--
Craig Ringer