On Wed, May 11, 2011 at 11:18 PM, Phoenix Kiula <phoenix.kiula@xxxxxxxxx> wrote: > 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! > My bad. I figured out that the pipe should only separate the strings to be searched. I had one stray pipe at the end: SELECT id FROM table1 WHERE mytext ~* E'sub1|sub2|sub3|....subXY|' LIMIT 10; This meant that it was matching, well basically anything. Sorry. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
