How to create a generalized regex (POSIX) in PostgreSQL?

Question

In postgreSQL (9.5), PgAdmin III, I would like to generalize this POSIX statement for two words:

This works for the words 'new' and 'intermediate' with word boundaries:

select * from cpt where cdesc ~* '^(?=.*\mnew\M)(?=.*\mintermediate\M)'

This fails ( the "where" argument is seen as a text string):

select * from cpt where cdesc ~* '^(?=.*\m'||'new'||'\M)(?=.*\mintermediate\M)'

How can this be written for a generlized function, e.g.:

CREATE OR REPLACE FUNCTION getDesc(string1 text, string2 text)
  RETURNS SETOF cpt AS
$BODY$ 

select * from cpt where cdesc ~* '^(?=.*\m$1\M)(?=.*\m$2\M)'

$BODY$
  LANGUAGE sql VOLATILE;

(where $1 is string1 and $2 is string2)

TIA

Edit. Match stings in cdesc would be:

"This is a new and intermediate art work"
"This is an intermediate and new piece of art"

Non-match would be:

"This is new art"
"This is intermediate art"

Please note the order of the words is not important as long as both are present. Also, either word may have a punctuation mark -- (comma or period)--immediately following the word (no space).

@MarZab Sorry for not including that. Please see Edit with match and non-match. Essentially, the words "new" and "intermediate" both need to be ANYWHERE in the sentence and a comma,"," or period , "." could immediately follow the word (without a space). Thanks. — Alan Wayne
– Alan Wayne, Commented Oct 15, 2016 at 13:55

MarZab · Accepted Answer · 2016-10-15 14:30:22Z

1

My first suggestion would be to split the expensive regex into two SQL WHERE clauses and:

matching with LIKE, as it is much faster, you can filter in code for more specific matches,
or matching with a simple regex, something like '\m$1[\M,.]'

As for the regex you are using:

I have not used it in a while, but I think you need parenthesis for string concatination

~* ( '^(?=.*\m' || 'new' || '\M)(?=.*\mintermediate\M)' )

answered Oct 15, 2016 at 14:30

MarZab

2,67823 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Alan Wayne Over a year ago

Yep...Just got that. How can I allow the word to allow a punctuation mark to immediately follow the word? Thanks.

MarZab Over a year ago

make a choice group, something like [\M,.]

Alan Wayne Over a year ago

Discovered that apparently \M already handles punctuation at end of word and there is no need for a choice group. Thanks.

Collectives™ on Stack Overflow

How to create a generalized regex (POSIX) in PostgreSQL?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related