• I am working in an android app which requires search functionality.
• As I am getting a nested list of data from server I have stored data in Room DB using Converters.
• Now my question is how can I search within the list stored in Room DB as I cannot fire query to compare a string with a list inside Room DB.
• How can I achieve that?

CodePudding user response：

An SQLite database has no concept of a list within a column it is just a stream/bock of data and if the List is converted then it is not suited to being searched as if it were a list.

You don't specify HOW you are converting the list into a stream of data which matters. However, if you are doing it the most common way then you will be storing a JSON string. Assuming that then you can use a query along the lines of

@Query("SELECT * FROM the_table WHERE the_column LIKE '%'||:searchString||'%')
fun doTheSearch(searchString: String): List<WhateverTheClassIsForTheTable>

• The || is concatenate
• The % is a wildcharacter
• if the search string were FRED then effectively the SQL would be LIKE %FRED%
  • LIKE is case insensitive as opposed to = which is case sensitive
• A wild character at the start requires a full table scan and is therefore relatively costly resource wise

This may well find unexpected items due to the JSON containing not only the actual data but the names of the components and various separators.

Additionally, it will not have a clue about your concept of a list and thus there will be no indication of which item in the list in which the result was found.