Обсуждение: Regexp match not working.. (SQL help)
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!
Phoenix Kiula <phoenix.kiula@gmail.com> writes: > 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. [ raised eyebrow... ] Could we see a concrete example? One potential issue is that, depending on which PG version and locale and database encoding you are using, case-insensitive matching might not work properly on non-ASCII letters. Other than that, the only gotcha I can think of is having regexp special characters in the substrings and failing to escape them properly. regards, tom lane
> > 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)...' > When requesting help with RegEx you are strongly advised to supply the text of the records that you are concerned about (in this case you say you are getting false positives so provide the contents of "mytext" for those records) AND the exact expression you are using. You seem to indicate the contents of mytext contains a "text document" and you are attempting to find specific words in that document. The expression format supplied does not take into consideration word boundaries. If any part of a word matches "subX" then the pattern will match. You may want to consider finding one or more books on RegEx. The fact that you consider E'(sub1)(sub2)...' to be a variation of E'sub1|sub2...' indicates that the issue is likely not PostgreSQL itself but your understanding on RegEx. You may also want to try the "regexp_matches(...)" function in PostgreSQL. Instead of just evaluating true/false it returns an array of all the matches that were found. Using this you would be able to see exactly what text PostgreSQL is matching with your expression. Figuring out why something is matching that should not be (false positive) is fairly easy since the engine itself will tell you what it matched. The hard situation is the false-negative, where you think something should match and it does not. David J.
On Wed, May 11, 2011 at 11:18 PM, Phoenix Kiula <phoenix.kiula@gmail.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.