>>>>> "Campbell" == Campbell, Lance <lance@illinois.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)
