Re: back references using regex

On Tue, Sep 06, 2005 at 11:40:18PM -0700, Matthew Peter wrote:
> I'm trying to do a slice directly from a table so I
> can get a brief preview of the articles content by
> counting \s (spaces), not new paragraphs.

Are you trying to extract the first N words from a string? If
that's not what you mean then please clarify.

> Anyone know how it could be done using regular
> expressions natively? I read the doc but it didn't
> help me much.

Regular expressions aren't the only way to solve the problem, but
maybe the following example will give you some ideas:

CREATE TABLE article (id integer, content text);

INSERT INTO article VALUES (1, 'one');
INSERT INTO article VALUES (2, 'one two');
INSERT INTO article VALUES (3, 'one two three');
INSERT INTO article VALUES (4, 'one two three four');
INSERT INTO article VALUES (5, 'one two three four five');
INSERT INTO article VALUES (6, 'one two three four five six');

SELECT id, substring(content FROM '(([^[:space:]]+[[:space:]]*){1,3})')
FROM article;
id | substring
----+----------------
1 | one
2 | one two
3 | one two three
4 | one two three
5 | one two three
6 | one two three
(6 rows)

In PostgreSQL 7.4 and later you could shorten the regular expression:

SELECT id, substring(content FROM '((\\S+\\s*){1,3})')
FROM article;

If this example isn't what you're looking for then please explain
what you're trying to do.

--
Michael Fuhr