Re: Help with a not match

>>>>> "Campbell" == Campbell, Lance <lance(at)illinois(dot)edu> writes:

Campbell> Very helpful. I am almost there.
Campbell> I created this SQL:

Campbell> SELECT regexp_matches(content, '/(?!files/'||id||'/)(files/\d+/)/', 'g') FROM tablea

Campbell> I get no matches. My guess is I am close but slightly off on
Campbell> the syntax.

Simplest regexp solution is to do this:

SELECT ... WHERE content ~ ('files/(?!' ||id|| '/)\d+/')

i.e. we're generating a regexp like 'files/(?!123/)\d+/' for each row.
No need for regexp_matches in this case because all we're looking for is
whether a match exists.

Another, possibly faster because it doesn't need a regexp compile for
each row, but possibly slower due to subplan overhead, would be:

SELECT ...
WHERE id::text <> ANY (SELECT (regexp_matches(content, 'files/(\d+)/', 'g'))[1])

The idea of the second method is to extract all the "NNN" values from
files/NNN/ substrings, and then test whether any NNN value is different
from the expected one. (This is a VERY RARE use of "<> ANY"; normally
one uses "<> ALL" as the negation of "= ANY", but the logic here
requires the negation of "= ALL" instead.)

--
Andrew (irc:RhodiumToad)