How to Use LIKE in Android Room Queries

By the end of this page, you will understand how LIKE works in SQL and how Room binds query parameters in Android. You will learn why %:arg0% does not work, how to concatenate wildcards correctly, and how to build contains, starts-with, and ends-with searches safely in Kotlin with Room.

In SQL, the LIKE operator is used for pattern matching on text values.

The wildcard characters are:

• % → matches any number of characters
• _ → matches exactly one character

For example:

name LIKE '%sam%'

matches:

• sam
• samantha
• hamsamster

In Android Room, query parameters such as :search are bound as values, not pasted into the SQL string as raw text. That means this is not valid:

name LIKE %:search%

because % is outside a valid SQL string expression.

And this is also wrong:

name LIKE '%:search%'

because is now inside quotes, so SQLite treats it as plain text instead of a parameter.

Think of LIKE as a text template.

• % means “anything can go here”
• your parameter is the exact text you want to search for

So if the user types ham, the pattern you want is:

%ham%

That means:

• anything before ham
• then ham
• then anything after it

Room does not let you glue % directly around a parameter by writing %:search%. Instead, you must build the pattern properly.

A simple way to picture it:

• Wrong: trying to stick labels onto a box from outside the box syntax
• Right: build one complete string pattern, then pass or construct it correctly

In other words, Room expects either:

• a ready-made search pattern, or
• SQL concatenation that produces one.

The most common correct syntax in Room is:

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

Example: contains search

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

If search is:

"ham"

then SQLite evaluates the pattern as:

%ham%

This matches names like:

• Hammy
• Mr Hamster
• Superham

Alternative: build the pattern in Kotlin

Consider this DAO method:

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

Assume the database contains:

• Hammy
• Tiny
• Mr Hamster
• Superham

And you call:

dao.loadHamsters("ham")

Step-by-step

1. Room sees the parameter :search.
2. It binds the value "ham" to that parameter.
3. SQLite evaluates this expression:

'%' || :search || '%'

1. That becomes:

%ham%

LIKE queries in Room are commonly used for local search features.

Search bars in apps

A user types part of a pet name, city, product, or contact name, and the app filters matching rows.

Example:

@Query("SELECT * FROM contacts WHERE fullName LIKE '%' || :query || '%' ORDER BY fullName")
fun searchContacts(query: String): Flowable<List<Contact>>

Autocomplete suggestions

Show matching items as the user types.

@Query("SELECT * FROM products WHERE title LIKE :query || '%' LIMIT 10")
fun suggestProducts(query: String): Flowable<List<Product>>

Settings or admin screens

Filter logs, categories, or records by text.

Offline-first apps

When an app stores data locally with Room, LIKE helps users search without making network requests.

Basic reporting tools

Users may search by customer name, order reference, or note content in a local database.

In real projects, developers usually combine LIKE with a few practical patterns.

1. Build safe search queries in DAO methods

A DAO often exposes a focused search method:

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

2. Normalize empty input

Instead of passing null, code often converts blank input to an empty string.

val query = userInput.trim()
dao.searchByName(query)

Since %""% becomes %%, it effectively matches all rows.

3. Use guard clauses in Kotlin

Some code decides whether to run a search query or a full-list query.

fun loadHamsters(search: String): Flowable<List<Hamster>> {
    val trimmed = search.trim()
    return if (trimmed.isEmpty()) {
        dao.loadAll()
    }  {
        dao.searchByName(trimmed)
    }
}

Here are the most common mistakes beginners make with LIKE in Room.

Mistake 1: Putting % directly around the parameter

Broken code:

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

Why it fails:

• % is not being used inside a valid SQL string expression
• Room cannot parse it correctly

Fix:

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

Mistake 2: Putting the parameter inside quotes

Broken code:

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

Here is how the main LIKE approaches compare.

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

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

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

// Alternative: build pattern in Kotlin
@Query("SELECT * FROM hamster WHERE name LIKE :pattern")
fun search(pattern: String): Flowable<List<Hamster>>

Rules to remember

• Use :search for Room parameters
• Do not write %:search%
• Do not write '%:search%'
• Use SQLite concatenation with

How do I use LIKE with parameters in Android Room?

Use SQLite string concatenation:

@Query("SELECT * FROM hamster WHERE name LIKE '%' || :search || '%'")

Why does %:search% fail in Room?

Because it is not valid SQL syntax. Room cannot parse % placed directly around a parameter like that.

Why does '%:search%' give an unused parameter error?

Because :search is inside quotes, so it becomes plain text instead of a bound parameter.

Can I build the % pattern in Kotlin instead?

Yes. Use LIKE :pattern and pass something like %ham% from Kotlin.

Should the search parameter be nullable?

Usually no. It is simpler to use a non-null String and pass an empty string when there is no filter.

Does LIKE ignore letter case?

Often for basic ASCII text in SQLite, yes, but not always in every situation. Test if case behavior matters for your app.

What if the user types or ?

• Room DAO queries — LIKE is written inside @Query methods, so understanding DAO syntax is essential.
• SQLite wildcards — % and _ define how pattern matching works.
• Parameter binding — Room uses :name placeholders, and knowing how binding works prevents syntax mistakes.
• SQL string concatenation — || is required when building LIKE patterns in SQLite.
• Null handling in Kotlin — nullable String? values can affect query behavior.
• Exact match vs pattern match — helps you choose between = and LIKE.
• Reactive database queries — Flowable<List<T>> is commonly used with Room to observe changing data.
• Search filtering — text matching is a core pattern in list filtering and search UIs.