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 :search is now inside quotes, so SQLite treats it as plain text instead of a parameter.

The correct approach is to either:

• build the wildcard pattern before passing it to the query, or
• concatenate % with the parameter inside SQL

For Room, a common correct version is:

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

Here, || is SQLite string concatenation. This creates a pattern like %abc% when search is abc.

This matters in real programming because text search is common in:

• search bars
• filtering lists
• autocomplete
• local database lookups
• admin panels and dashboards

Understanding parameter binding also helps prevent SQL mistakes and keeps queries safe and maintainable.

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

You can also do this:

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

Then call it like this:

val pattern = "%${search}%"
dao.loadHamsters(pattern)

Starts with

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

Matches names beginning with the text.

Ends with

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

Matches names ending with the text.

About nullable parameters

Your original function used String?:

fun loadHamsters(search: String?): Flowable<List<Hamster>>

If search can be null, LIKE may not behave as you expect because LIKE NULL does not match text. If possible, prefer a non-null String and pass an empty string when you want all results.

Example:

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

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%

1. SQLite checks each name value against %ham%.
2. Matching rows are returned.

Match trace

Important note

Case sensitivity can vary depending on SQLite settings and collation rules. For simple English text, LIKE is often case-insensitive for ASCII characters, but you should test behavior if exact case handling matters in your app.

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()
    } else {
        dao.searchByName(trimmed)
    }
}

4. Keep SQL simple

Many teams prefer:

• simple DAO SQL
• input cleanup in Kotlin
• ordering and limits in the query

5. Combine with reactive streams

Your example uses Flowable<List<Hamster>>, which is common when the UI should update automatically when database contents change.

6. Handle special characters when needed

If user input may contain % or _, some code escapes those characters so they are treated literally rather than as wildcards. That becomes important in advanced search behavior.

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: String): Flowable<List<Hamster>>

Why it fails:

• :search becomes plain text inside the string
• Room no longer sees it as a parameter
• you get an unused parameter error

Fix:

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

Mistake 3: Using a nullable parameter without thinking about null

Broken or confusing code:

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

Why it can be a problem:

• if search is null, the result may not match anything as expected

Safer approach:

fun loadHamsters(search: String)

Or handle null before calling the DAO.

Mistake 4: Forgetting that % and _ are wildcards

If a user searches for % or _, the result may match more rows than expected.

Mistake 5: Expecting complex search behavior from basic LIKE

LIKE is fine for simple text matching, but it is not a full-text search engine.

For beginner use cases, LIKE is usually enough. For large advanced search features, other tools may be more appropriate.

Here is how the main LIKE approaches compare.

LIKE vs =

SQL concatenation vs quoted placeholder

// 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 ||
• Prefer String over String? unless you really need null
• % matches zero or more characters
• _ matches exactly one character

Correct fix for the original problem

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

If input may be blank

val safeSearch = userInput.orEmpty()
dao.loadHamsters(safeSearch)

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

Those are wildcard characters in LIKE, so they may match more than you expect unless escaped.

Is LIKE good for full search features?

It is good for simple filtering and local search. For advanced search across large text fields, you may need something more specialized.

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