3

I would like to select more features with expression in a specific field, which contains the name of the attribute (IN operator is useful). Moreover, the name could not be exact, so the ILIKE operator is necessary.

How can I match these 2 operators in one expression?

For example, I need to select 2 city called 'Rome' and 'New York'.

"city" in ('Rome','New York') -> selected both
"city" in ('rom','new yor') -> not selected

Any suggestion to select in the second case in QGIS?

1
  • The select by expression uses its own non-SQL syntax
    – JGH
    Jun 1, 2020 at 12:07

2 Answers 2

5

You can achieve this task using arrays.

  1. Create an array with the search themes AND the placeholders
  2. for each value of the array, find if the name column contains it or not, using ilike
  3. as the output of step 2 is an array of true/false values, check if this output contains at least 1 true result
array_contains(
    array_foreach( 
        ARRAY('%rom%','new yor%'),
        name ilike @element),
true)
2
  • How can i select, using the expression, the records which cannot contain '%rom%','new yor%'? Replacing true with false and using "not ilike" it doesn't work. I need the invert selection of the otuput. Thanks!
    – Mark
    Oct 1, 2021 at 14:45
  • @Mark keep the same expression and add NOT in front
    – JGH
    Oct 1, 2021 at 14:56
1

Additionally you may try using regexp_match() function

regexp_match("City", '((?i)new yor|(?i)rom)')

The result of this query will select 'Rome' and 'New York' including their derivatives, see image below

result


References:

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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