Re: Regexp match not working.. (SQL help)

On Wed, May 11, 2011 at 11:18 PM, Phoenix Kiula <phoenix(dot)kiula(at)gmail(dot)com> 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.