SQLite and Room Persistence Library: how to combine IN condition and LIKE?

Question

I'm having trouble "Googling" this one, but hopefully it's not too tricky for an expert (or even an intermediate).

Is there a way to search for a substring within an unknown number of multiple OR conditions?

The original query for the Android SQLite Room Persistence DAO is:

SELECT country FROM table WHERE country IN (:searchList)

Which, given a searchList of ['Paris, France', 'Berlin, Germany'] would translate to the SQL:

SELECT country FROM table WHERE country IN ( 'Paris, France', 'Berlin, Germany' );

And would return: ['Paris, France', 'Berlin, Germany']

However, I would like to be able to search by just the country. For example, given a searchList of ['France', 'Germany'], I unfortunately get no result.

I understand the following SQL would do the trick:

SELECT country FROM table  WHERE country LIKE '%France%' OR country LIKE '%Germany%';

However, because I don't know how many elements would be in the searchList, I have to use the original format:

SELECT country FROM table WHERE country IN (:searchList)

Is there any way to make the above search for a substring within the country column?

Many thanks for your help,

Dan

Answer 1

Since Androids Sqlite is not shipped with Regex you will need to split the list before you search it.

SQLite IN-Operator sorts your list and use a binary search which is by far faster then using single queries for this case.

Performance OR vs IN to compare the speed (unfortunately not for batch queries)

SELECT COUNT(*) FROM t_inner WHERE val IN (1000, 2000, 3000, 4000, 5000, 6000, 7000, 8000, 9000);
1 row fetched in 0.0032 (1.2679 seconds)

SELECT COUNT(*) FROM t_inner WHERE val = 1000 OR val = 2000 OR val = 3000 OR val = 4000 OR val = 5000 OR val = 6000 OR val = 7000 OR val = 8000 OR val = 9000;
1 row fetched in 0.0026 (1.7385 seconds)