4

Context

I use QGIS expressions and want to use regular expressions to identify those strings that contain two words. It's easy to identify those features of the field text that contain both QGIS or PostGIS with this expression:

regexp_match("text", ('QGIS|PostGIS'))>0

Using with the following four features, no 1. 2 and 3 return 1, no. 4 returns 0.

1: 'This text contains the words "QGIS" and "PostGIS" as well as others.'

2: 'This text contains the word "QGIS" as well as others.'

3: 'This text contains the word "PostGIS" as well as others.'

4: 'This text contains differents words.'

Question

I now want to identify features that contain both words - thus not QGIS or PostGIS, but QGIS and PostGIS - so only line 1 should return 1, all others 0. So if using Regular expressions, the logical operator OR is |, I'm looking for the equivalent for the operator AND.

In this PCRE Regex Cheatsheet, there is an example for OR: a|b -> a or b, but there is no and operator. How to construct an and connection?

Improve this question
1
  • 1
    There is no standard regex which implements AND, but there is nothing which prevents it from being implemented. In actual practice, it's very hard to come up with real-life scenarios where it would really be practical.
    – tripleee
    Commented Dec 28, 2021 at 12:33

2 Answers 2

Reset to default
3

You're right. There is no and operator.
You need to do a lookahead for it to work:

regexp_match("text", '=?.*QGIS=?.*PostGIS')>0

I thought it should work regardless of the words' order, but it seems it doesn't. For that, you can add the or:

regexp_match("text", '=?.*(QGIS|PostGIS)=?.*(PostGIS|QGIS)')>0

Edit

Following a comment by @tripleee, there is indeed another way to do this, which simplifies the expression above (I just edited it for use in QGIS):

regexp_match("text", '(QGIS.*PostGIS)|(PostGIS.*QGIS)')>0

Improve this answer
4
  • 1
    This is slightly imprecise still. You would probably want to look for QGIS.*PostGIS|PostGIS.*QGIS in the lookahead.
    – tripleee
    Commented Dec 28, 2021 at 12:34
  • What is `\\` for? And, @tripleee - how exactly would the whole regex look like that you propose?
    – Babel
    Commented Dec 28, 2021 at 18:26
  • 1
    Actually that could be the entire expression if you just want to check whether both are present. So regexp_match("text", ('QGIS.*PostGIS|PostGIS.*QGIS ')>0 which can be read out as QGIS followed by anything followed by PostGIS, or PostGIS followed by anything followed by QGIS. I don't know enouga about the system to tell whether this is sufficient (I came here from another site because this question came up in Hot Network Questions and picked my curiosity).
    – tripleee
    Commented Dec 28, 2021 at 18:29
  • 1
    @Babel The `\` are not really needed in this example, because they are used as escape characters. I edited the answer to also include the solution proposed by @tripleee with minor adjustments for it to work in QGIS.
    – Cezar B
    Commented Dec 29, 2021 at 1:20
2

An alternative:

https://docs.qgis.org/testing/en/docs/user_manual/expressions/functions_list.html#id60

"text" ~ 'PostGIS' AND "text" ~ 'QGIS'

enter image description here

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.