Regexp match not working.. (SQL help)

From: Phoenix Kiula <phoenix(dot)kiula(at)gmail(dot)com>
To: PG-General Mailing List <pgsql-general(at)postgresql(dot)org>
Subject: Regexp match not working.. (SQL help)
Date: 2011-05-11 15:18:35
Message-ID: BANLkTin1BDzYvu_0GuLhY+pyqFfNLx3jQg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I have a text column in a table, which I want to search through --
seeking the occurrence of about 300 small strings in it.

Let's say the table is like this:

table1 (
id bigint primary key
,mytext text
,mydate timestamp without time zone
);

I am using this SQL:

SELECT id FROM table1
WHERE mytext ~* E'sub1|sub2|sub3|sub4...'
LIMIT 10;

This is basically working, but some of the "mytext" columns being
returned that do not contain any of these substrings. Am I doing the
POSIX regexp wrongly? This same thing works when I try it in PHP with
preg_match. But not in Postgresql. I have tried several variations
too:

WHERE mytext ~* E'(sub1)(sub2)(sub3)(sub4)...'

None of this is working. I cannot seem to get out the results that do
NOT contain any of those strings.

Appreciate any pointers!

Thanks!

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Alex - 2011-05-11 15:22:11 Query to return every 1st Sat of a month between two dates
Previous Message Pavel Stehule 2011-05-11 15:07:16 Re: full text search to_tsquery performance with ispell dictionary