Re: back references using regex - Mailing list pgsql-general

From Michael Fuhr
Subject Re: back references using regex
Date
Msg-id 20050907192454.GA20901@winnie.fuhr.org
Whole thread Raw
In response to back references using regex  (Matthew Peter <survivedsushi@yahoo.com>)
Responses Re: back references using regex
List pgsql-general
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

pgsql-general by date:

Previous
From: J
Date:
Subject: max number of triggers in a table
Next
From: Steve Atkins
Date:
Subject: Re: Email Verfication Regular Expression