Selecting more features with "ILIKE" and "IN" operators using QGIS

Question

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?

JGH · Accepted Answer

You can achieve this task using arrays.

Create an array with the search themes AND the placeholders
for each value of the array, find if the name column contains it or not, using ilike
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)

Taras · Answer

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

References:

https://github.com/qgis/QGIS/blob/master/resources/function_help/json/regexp_match

Selecting more features with "ILIKE" and "IN" operators using QGIS

2 Answers

Add your own answers!

Ask a Question