agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedFrom: Andrew Gierth <andrew@tao11.riddles.org.uk>
To: Mark Williams <markwillimas@gmail.com>
Cc: pgsql-sql@lists.postgresql.org
Subject: Re: Regular Expressions
Date: Mon, 05 Nov 2018 17:51:19 +0000
Message-ID: <878t272zlw.fsf@news-spur.riddles.org.uk> (raw)
In-Reply-To: <012401d47472$0ebc9240$2c35b6c0$@gmail.com>
References: <012401d47472$0ebc9240$2c35b6c0$@gmail.com>
>>>>> "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)
view thread (18+ messages)
Message-ID: <878t272zlw.fsf@news-spur.riddles.org.uk>
Permalink: ../878t272zlw.fsf@news-spur.riddles.org.uk/
Also on: postgresql.org/message-id/878t272zlw.fsf@news-spur.riddles.org.uk
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: andrew@tao11.riddles.org.uk, markwillimas@gmail.com, pgsql-sql@lists.postgresql.org
Subject: Re: Regular Expressions
In-Reply-To: <878t272zlw.fsf@news-spur.riddles.org.uk>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox