Android Room - Select query with LIKE

Question

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?

score 469 · Answer 1 · edited Nov 30 '18 at 01:07

469

You can just concat using SQLite string concatenation.

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

edited Nov 30 '18 at 01:07

em_

2,134
2
24
39

answered Jun 09 '17 at 02:55

yigit

37,683
13
72
58

1

work even with kotlin bug: youtrack.jetbrains.com/issue/KT-17959 – Andriy Antonov Mar 16 '18 at 07:15
11

This approach is actually what the question was asking for. Although the other answer is quite good too. – xarlymg89 Jul 18 '18 at 09:42
4

I understand the `'%'` but can someone explain what is `'||'` and why? – Ali Kazi Jan 29 '19 at 05:50
41

`||` is string concatenate operator. Think of it as `+` in Java String. – Sanlok Lee Feb 12 '19 at 04:27
@SanlokLee But in answer, '%' is char type not string. Why it doesn't throw an error when concatenating string with char? – Rohit Sawai Feb 12 '20 at 04:23
@RohitSawai, the query expression is in SQLite. SQLite uses single quotation marks to represent string literals. – Sanlok Lee Feb 12 '20 at 05:01
@SanlokLee You didn't understand my question. We pass String value to Query annotation. But in that passed query, we wanna pass % as we need in Like operation but we have quoted % sign in single quote instead of double and have used unfamiliar || pipe operators as well. My question is why we used single quote instead of double around % operator? – Rohit Sawai Feb 12 '20 at 06:52
I am sorry if my prev answer wasn't clear enough but I will just have to repeat my last sentence: **SQLite uses single quotation marks to represent string literals.** You wrote "_We pass String value to Query annotation_", and the String you mentioned represents an SQLite statement. You don't use double quotes here because the _content_ of the string is supposed to be an SQLite statement, not Java nor Kotlin syntax. – Sanlok Lee Feb 13 '20 at 00:35
this is kotlin, so **for java** should it be like this: `@Query("SELECT * FROM CarSales where vechno like :vechno1 ORDER BY vechno") public abstract List searchVehno(String vechno1); //**add % in the vaue passed as-> vehno1+"%" in function call**` – AndroidLearner Feb 16 '21 at 08:29

score 201 · Accepted Answer · answered May 25 '17 at 16:25

201

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

answered May 25 '17 at 16:25

Cody Caughlan

32,456
5
63
68

5

Note that there is currently a bug with parameter names in Kotlin's annotation processing: https://youtrack.jetbrains.com/issue/KT-17959 – Kirill Rakhman May 25 '17 at 19:16
@KirillRakhman but this method still works fine ;-) https://stackoverflow.com/a/44448566/6674369 – Andriy Antonov Mar 16 '18 at 07:00
@AndriyAntonov the Kotlin bug was fixed 9 months ago – Kirill Rakhman Mar 16 '18 at 09:28

score 47 · Answer 3 · edited Jul 25 '22 at 12:38

Some Examples of LIKE Keywords used in dao with Room Database.

Start with Search_Query

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

End with Search_Query

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

Get all that have Search_Query

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

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

|| help us concat the string, so '%' || 'abc' || '%' == '%abc%'. — JeckOnly, Oct 12 '22 at 15:10

score 1 · Answer 4 · answered May 08 '19 at 10:01

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

Android Room - Select query with LIKE

4 Answers4

Linked

Related