CODEWITHSUNDEEP
androidkotlinandroid-room
Denis Buzmakov
Denis Buzmakov

Reputation: 1842

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?

Upvotes: 184

Views: 84451

Answers (4)

Umesh Yadav
Umesh Yadav

Reputation: 1200

Some Examples of LIKE Keywords used 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>>
  1. End with Search_Query
@Query("SELECT * FROM hamster WHERE name LIKE '%' || :search_query ")
   fun loadHamsters(search_query: String?): Flowable<List<Hamster>>
  1. Get all that have Search_Query
@Query("SELECT * FROM hamster WHERE name LIKE '%' || :search_query || '%'")
   fun loadHamsters(search_query: String?): Flowable<List<Hamster>>
  1. 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>>

Upvotes: 60

Swapnil Mobile App Developer
Swapnil Mobile App Developer

Reputation: 810

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);

Upvotes: 1

yigit
yigit

Reputation: 38253

You can just concat using SQLite string concatenation.

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

Upvotes: 491

Cody Caughlan
Cody Caughlan

Reputation: 32748

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>>

Upvotes: 202

Related Questions