From: Andrew Gierth <andrew@tao11.riddles.org.uk>
To: "Mark Williams" <markwillimas@gmail.com>
Cc: <pgsql-sql@lists.postgresql.org>
Subject: Re: Regular Expressions
In-Reply-To: <012401d47472$0ebc9240$2c35b6c0$@gmail.com> (Mark Williams's
	message of "Sun, 4 Nov 2018 19:10:31 -0000")
Message-ID: <878t272zlw.fsf@news-spur.riddles.org.uk>
References: <012401d47472$0ebc9240$2c35b6c0$@gmail.com>
User-Agent: Gnus/5.13 (Gnus v5.13) Emacs/25.1 (berkeley-unix)
Date: Mon, 05 Nov 2018 17:51:19 +0000
MIME-Version: 1.0
Content-Type: text/plain
Precedence: bulk

>>>>> "Mark" == Mark Williams <markwillimas@gmail.com> writes:

 Mark> I can't figure out how to search myfield for all instances which
 Mark> contain "text1" AND "text2".

I should start by pointing out that (as mentioned by other people) using
regexps is not necessarily the best way to do this, especially not when
dealing with actual words which is what FTS exists for.

But a solution does exist (at least in pg and other regexp engines that
support lookahead assertions):

myfield ~* '^(?=.*\mtext1\M)(?=.*\mtext2\M)'

What this says is: match at the start of the string if (and only if)
both the lookahead assertions succeed; since neither assertion advances
the match, they will find the two specified words regardless of the
order in which they appear. (The trick of using | to search for both
possible orders works for 2 words, but gets unwieldy very quickly with
more; with the assertion method you can handle any number of words.)

-- 
Andrew (irc:RhodiumToad)