Thread: Regular Expressions

If I wanted to search for whole words in a field I would use something like:

 

Select * from mytable where myfield ~* ‘(\mtext1\M) | (\mtext2\M)’

 

This would find all instances of myfield containing either “text1” or “text2”.

 

I can’t figure out how to search myfield for all instances which contain “text1” AND “text2”.

 

In other words | is the OR operator. What is the AND operator. Tried + and whilst that executes, it doesn’t return matching fields.

 

Thanks

 

Mark

 

__

 

I can’t figure out how to search myfield for all instances which contain “text1” AND “text2”.

In other words | is the OR operator. What is the AND operator. Tried + and whilst that executes, it doesn’t return matching fields.

=====================

Myfield ~* ‘text1’ AND myfield ~* ‘text2’

There is no convenient concept of AND in the sense you want it in Regular Expressions and “+” has its own meaning of “one or more of the previous item”.   

David J.

Also, what would be the regular expression if you want to check whether all the words were in the field where you had say 10 words/phrases you wanted to check for?

==============

Consider full text search instead.

Maybe “split_to_array” on spaces and then do something like:

ARRAY[‘term1’, ‘term2’]::text[] && split_to_array(field, ‘ ‘)

David J.

"Mark Williams" <markwillimas@gmail.com> writes:
> Also, what would be the regular expression if you want to check whether all the words were in the field where you had
say10 words/phrases you wanted to check for? 

As David said, regular expressions aren't really designed to do that.
Personally I'd do the AND at the SQL level, ie

myfield ~* '\mtext1\M' AND myfield ~* '\mtext2\M' AND ...

You might also take a look at the full text search machinery, which
is probably better suited to this task.

            regards, tom lane

Thanks all. Have implemented by way of "AND" for time being. Full text
search on my list of todos!

__

-----Original Message-----
From: Tom Lane <tgl@sss.pgh.pa.us> 
Sent: 04 November 2018 19:55
To: Mark Williams <markwillimas@gmail.com>
Cc: 'A. Sasaki' <asasaki@gmail.com>; pgsql-sql@lists.postgresql.org
Subject: Re: Regular Expressions

"Mark Williams" <markwillimas@gmail.com> writes:
> Also, what would be the regular expression if you want to check whether
all the words were in the field where you had say 10 words/phrases you
wanted to check for?

As David said, regular expressions aren't really designed to do that.
Personally I'd do the AND at the SQL level, ie

myfield ~* '\mtext1\M' AND myfield ~* '\mtext2\M' AND ...

You might also take a look at the full text search machinery, which is
probably better suited to this task.

            regards, tom lane

>>>>> "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)