Room SQLite Database - Delete all LIKE %:param%

Question

I have an issue with Room which I haven't been able to find help for elsewhere - the usual '%' for matching any number of characters when using 'LIKE' in SQLite doesn't work with the Room delete query we are using in my team's project.

We have a delete query defined as a string in Kotlin, in one of our DAOs:

const val DELETE_BY_RESERVATION_CODE = "DELETE FROM ${TdaRoomDatabase.TABLE_REQUESTS} WHERE uri LIKE :reservationCode"

And a deleteRequest in the same DAO:

@Query(DELETE_BY_RESERVATION_CODE)
fun deleteRequest(reservationCode: String)

However, after adding the percent symbols around the ':reservationCode' parameter reference we get the following error:

No viable alternative at input 'DELETE FROM requests WHERE uri LIKE %'

Is anyone aware of how to use the percent symbol when using 'LIKE' in a Room DB query? My team and I (not well-versed in SQL) expected it to behave like SQLite but this doesn't seem to be the case!

Possible duplicate of [Android Room - Select query with LIKE](https://stackoverflow.com/questions/44184769/android-room-select-query-with-like) — earthw0rmjim, May 02 '18 at 15:32
Our input query is based on a string from a network response and I think it's quite ugly to surround the input query using something like `"%" + input + "%"` — Bryen, May 02 '18 at 15:39
However I've found a prettier solution which deals with the problem within the DAO instead of wrapping the input with "%" [Link to the better solution](https://stackoverflow.com/questions/44234644/android-rooms-search-in-string) — Bryen, May 02 '18 at 15:40
Thanks for the help, I've edited the question to help any others who encounter the issue. — Bryen, May 02 '18 at 15:51

score 6 · Accepted Answer · answered May 02 '18 at 15:54

Thanks for your help! I have found two solutions - one was described in the comments, which involves wrapping the input parameter with '%' when calling the DAO method.

The solution which I've found to be somewhat more elegant is stackoverflow.com/questions/44234644/android-rooms-search-in-string, which keeps the input selector symbols within the query string in the DAO, rather than wrapping the input parameter wherever the DAO method is called.

This meant the DAO string ended up looking like this:

const val DELETE_BY_RESERVATION_CODE = "DELETE FROM ${TdaRoomDatabase.TABLE_REQUESTS} WHERE uri LIKE '%' || :reservationCode || '%'"

Thank you to user [yigit](https://stackoverflow.com/users/608649/yigit) for the helpful answer! — Bryen, May 02 '18 at 15:57

Room SQLite Database - Delete all LIKE %:param%

1 Answers1