5

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

danwag
  • 355
  • 2
  • 14
  • Since androids sqlite (version) does not support regexp in the search query you may want to split the List (:searchList) to single words instead? Example: https://stackoverflow.com/questions/41678776/substring-for-sqlite-android – Emanuel Aug 22 '17 at 10:59
  • I was hoping to avoid splitting searchList, because then I'd have to perform multiple calls to the DB - one for each element of searchList. – danwag Aug 22 '17 at 11:03

1 Answers1

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)
Emanuel
  • 8,027
  • 2
  • 37
  • 56
  • Thank you for your answer. That is a shame though :) I guess what I will do is add a country row for each entry, so a UID would be linked to both "Berlin, Germany" and "Germany". – danwag Aug 22 '17 at 11:13
  • Yes i know, but this is Room.. I use it for very large projects and its stable so far, but that doesnt mean that its final now ;-) There are some ports of Sqlite which supports regex. – Emanuel Aug 22 '17 at 11:14
  • You may want to benchmark the speed if you get all results or at least bunchwise and use a selection / filter after. – Emanuel Aug 22 '17 at 11:16
  • Good point. I'll keep an eye out for updates, but for now the additional row should do the trick. – danwag Aug 22 '17 at 11:16
  • It's not so much the speed that's a problem, there should never be more that ~20 locations to search, it's just the variability - I don't know how many locations I'll be searching for, so I can't create the SQL with the correct number of ORs beforehand. – danwag Aug 22 '17 at 11:19
  • Then i would recommend selecting all and filtering after. – Emanuel Aug 22 '17 at 11:20