Android Room - Select query with LIKE

I'm trying to make a query to search all objects whose names contain text:

@Query("SELECT * FROM hamster WHERE name LIKE %:arg0%")
fun loadHamsters(search: String?): Flowable<List<Hamster>>

Messages:

Error:no viable alternative at input 'SELECT * FROM hamster WHERE name LIKE %'
Error:There is a problem with the query: [SQLITE_ERROR] SQL error or missing database (near "%": syntax error)
Error:Unused parameter: arg0

Also I'm trying:

@Query("SELECT * FROM hamster WHERE name LIKE '%:arg0%'")
fun loadHamsters(search: String?): Flowable<List<Hamster>>

Messages:

Error:Unused parameter: arg0

How to fix this?

You can just concat using SQLite string concatenation.

@Query("SELECT * FROM hamster WHERE name LIKE '%' || :search || '%'")
fun loadHamsters(search: String?): Flowable<List<Hamster>>

You should enclose the % characters in your input query - not in the query itself.

E.g. try this:

@Query("SELECT * FROM hamster WHERE name LIKE :arg0")
fun loadHamsters(search: String?): Flowable<List<Hamster>>

Then your String search value should look like:

search = "%fido%";
loadHamsters(search);

Furthermore, the binding parameter name should match the variable name, so rather than arg0 it should look like:

@Query("SELECT * FROM hamster WHERE name LIKE :search")
fun loadHamsters(search: String?): Flowable<List<Hamster>>

Some Example of LIKE Keyword using in dao with Room Database.

1. Start with Search_Query

   @Query("SELECT * FROM hamster WHERE name LIKE :search_query || '%'")
    fun loadHamsters(search_query: String?): Flowable<List<Hamster>>
   

2. End with Search_Query

   @Query("SELECT * FROM hamster WHERE name LIKE '%' || :search_query ")
   fun loadHamsters(search_query: String?): Flowable<List<Hamster>>
   

3. Get all which have Search_Query

   @Query("SELECT * FROM hamster WHERE name LIKE '%' || :search_query || '%'")
   fun loadHamsters(search_query: String?): Flowable<List<Hamster>>
   

4. Use NOT LIKE For except data which have Search_Query

   @Query("SELECT * FROM hamster WHERE name NOT LIKE '%' || :search_query || '%'")
   fun loadHamsters(search_query: String?): Flowable<List<Hamster>>
   

Room only supports named bind parameter :name to avoid any confusion between the method parameters and the query bind parameters.

Room will automatically bind the parameters of the method into the bind arguments. This is done by matching the name of the parameters to the name of the bind arguments.

 @Query("SELECT * FROM user WHERE user_name LIKE :name AND last_name LIKE :last")
 public abstract List<User> findUsersByNameAndLastName(String name, String last);