Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1gLEtQ-0005XK-KX for pgsql-sql@arkaria.postgresql.org; Fri, 09 Nov 2018 22:04:44 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1gLEtO-0004AD-Oe for pgsql-sql@arkaria.postgresql.org; Fri, 09 Nov 2018 22:04:42 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1gLEtO-0004A6-EJ for pgsql-sql@lists.postgresql.org; Fri, 09 Nov 2018 22:04:42 +0000 Received: from lungold.riddles.org.uk ([82.68.208.19]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1gLEtJ-000236-Tm for pgsql-sql@postgresql.org; Fri, 09 Nov 2018 22:04:41 +0000 Received: from [192.168.127.1] (port=13565 helo=caithnard.riddles.org.uk) by lungold.riddles.org.uk with esmtps (TLSv1.2:ECDHE-RSA-AES256-GCM-SHA384:256) (Exim 4.88 (FreeBSD)) (envelope-from ) id 1gLEtG-000Am3-2O; Fri, 09 Nov 2018 22:04:34 +0000 Received: from [127.0.0.1] (port=39491 helo=caithnard.riddles.org.uk) by caithnard.riddles.org.uk with esmtp (Exim 4.89 (FreeBSD)) (envelope-from ) id 1gLEtF-000Ckf-9c; Fri, 09 Nov 2018 22:04:33 +0000 From: Andrew Gierth To: "Campbell\, Lance" Cc: "David G. Johnston" , pgsql-sql Subject: Re: Help with a not match In-Reply-To: (Lance Campbell's message of "Fri, 9 Nov 2018 17:59:07 +0000") Message-ID: <87a7mix73u.fsf@news-spur.riddles.org.uk> References: <47589FBF-7910-4F95-8239-BDF702A19B2C@illinois.edu> User-Agent: Gnus/5.13 (Gnus v5.13) Emacs/25.1 (berkeley-unix) Date: Fri, 09 Nov 2018 22:04:32 +0000 MIME-Version: 1.0 Content-Type: text/plain List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk >>>>> "Campbell" == Campbell, Lance 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)