SQLite LIKE: Powerful Pattern Matching SQLite Queries

Searching databases can be tedious. You’re often stuck scanning every row manually to find what you need. But what if you could search for patterns instead of exact values?

Enter SQLite LIKE. LIKE brings pattern-matching capabilities to SQLite, allowing you to find records that match a specified pattern. Whether you need to search for names starting with “A” or email addresses ending in “.com”, LIKE has you covered.

So let’s dive in and unlock the full potential of pattern matching in SQLite!

Also read: The SQLite Handbook: A Start-to-Finish Resource for Learning and Using SQLite

A Quick Intro to SQLite LIKE

Before we get into LIKE syntax and usage, let’s do a quick overview of what LIKE actually does.

The LIKE operator allows you to match parts of values based on wildcard characters rather than exact matches.

Here’s a simple example searching for employee names starting with “J”:

SELECT * FROM employees
WHERE name LIKE 'J%';

This will match names like “John”, “Jane”, “Josiah”, etc. The % wildcard matches any number of characters after “J”.

The real power of LIKE comes from using wildcards to match patterns instead of exact values. This gives you tremendous flexibility when querying databases.

Now let’s look at the syntax and usage in more detail.

SQLite LIKE Syntax and Usage

The LIKE syntax is straightforward:

expression LIKE pattern


  • expression is the value you want to match against
  • pattern is the matching pattern with wildcards

For example:

name LIKE 'J%'

This checks if name matches the pattern ‘J%’.

You can use LIKE in SELECT, UPDATE, DELETE, etc. Anywhere you can filter data with a WHERE clause, you can use LIKE for pattern matching.

Now let’s see how to use wildcards for powerful pattern matching.

SQLite LIKE Wildcards for Pattern Matching

SQLite LIKE supports two wildcards for pattern matching:

  • % – Matches zero or more characters
  • _ – Matches a single character

These two wildcards allow you to create flexible matching patterns.

The % Wildcard

The % wildcard matches any number of characters, including zero. This allows you to match values that:

  • Start with a pattern
  • End with a pattern
  • Contain a pattern

Here are some examples:

-- Names starting with "J"
name LIKE 'J%' 

-- Names ending in "son"
name LIKE '%son'

-- Names containing "ohn"
name LIKE '%ohn%'

You can see how % gives you tremendous flexibility.

The _ Wildcard

The _ wildcard matches exactly one character. This allows you to specify pattern matching at the character level.

For example:

-- 5 letter names starting with "S" and ending in "th"
name LIKE 'S___th'

-- Names with "hn" as 2nd and 3rd characters
name LIKE '_hn%' 

While % gives you flexibility, _ allows precise single-character matching.

Combining Wildcards with SQLite LIKE

You can combine % and _ in the same pattern for very powerful matching capabilities:

-- Emails ending in ".com" with one character before 
email LIKE '%_@%.com'

-- Prices rounded to nearest 10
price LIKE '__0' 

With %_ and combinations of both, you can match a wide variety of patterns in your data using SQLite LIKE.

SQLite LIKE Examples for Common Patterns

Now let’s go through some common examples of using SQLite LIKE for real-world patterns:

Matching Text Starting/Ending with Pattern

Find names starting or ending with a specific text pattern:

-- Names starting with "A"
name LIKE 'A%'

-- Names ending with "ton" 
name LIKE '%ton' 

Matching Text Containing Pattern

Find text containing a specific pattern anywhere:

-- Emails containing "@gmail"
email LIKE '%@gmail%' 

-- Names containing "jo"
name LIKE '%jo%'

Matching Patterns with Fixed Length

Match fixed-length patterns with _:

-- 5-digit zip codes 
zip LIKE '_____' 

-- Emails with 8-letter domain name 
email LIKE '%@______.com'

Matching Numeric Patterns

Match numeric patterns and ranges:

-- Prices between $10 - $20
price BETWEEN 10 AND 20

-- Numbers starting with 4, 5, 6 
num LIKE '[456]%'

These are just a few examples of common patterns you can match with LIKE.

Using SQLite LIKE on Different Data Types

So far, we’ve looked at using LIKE on text columns. But you can use LIKE on other data types as well:

  • Numeric – Match numeric ranges and patterns
  • Date/time – Match date ranges and partial dates
  • Blob – Match based on partial byte matching

Here are some examples:

-- Date in October 2022
date LIKE '2022-10%' 

-- Price between 5000 and 6000  
price BETWEEN 5000 AND 6000

-- Blob starting with 0x0201AF
blob_data LIKE x'0201AF%' 

While SQLite LIKE excels at matching text patterns, it’s also handy for filtering other data types.

Optimizing SQLite LIKE Query Performance

SQLite LIKE queries can sometimes get slow since they often perform full table scans. Here are some tips to optimize performance:

  • Use indices – Adding an index on the searched columns can speed up LIKE.
  • Prefix match – Prefix searches with % at the end are fastest.
  • Avoid starting with wildcard – Patterns like %something are slow.
  • Use full-text search – Modules like FTS5 offer more optimized search.
  • Limit search set – Add a WHERE clause to filter rows before LIKE.

With large datasets, take care to optimize your LIKE queries. Pay attention to slow queries and tweak your patterns and indexes accordingly.

Common Mistakes to Avoid With SQLite LIKE

While SQLite LIKE is powerful, there are some common mistakes that can trip you up:

  • Forgetting that LIKE is case-insensitive by default. Add PRAGMA case_sensitive_like = true; to make it case-sensitive.
  • Assuming % wildcard matches just one character instead of zero or more.
  • Using _ wildcard for matching zero characters instead of just one.
  • Forgetting to escape % or _ characters used as literals in the search pattern.
  • Overusing % wildcards which can negatively impact performance.

By being aware of these pitfalls, you can avoid headaches down the line!

Key Takeaways

Here are the key takeaways about mastering SQLite LIKE:

  • LIKE enables powerful pattern matching using % and _ wildcards
  • You can search for patterns at the start, end, or anywhere in values
  • LIKE works on text, numeric, date, and blob data types
  • Optimize performance with indices, prefix searches, and full-text search
  • Avoid common mistakes like case sensitivity, wildcard usage, and escaping


LIKE is an invaluable tool for pattern matching in SQLite. By mastering its wildcards and usage, you can find exactly what you need from any dataset.

We covered how LIKE works, its syntax, wildcards, data type usage, optimizations, and common mistakes. With % and _, you can match text, numeric, date, and blob patterns with ease.

The key is understanding that LIKE enables you to search based on patterns, not just exact values. This flexibility supercharges your queries and unlocks the real power of your databases.

Whether you need to query names, emails, prices, zip codes or anything else, LIKE has you covered. So be sure to add this mighty operator to your SQLite skillset.

By combining LIKE with indices, full-text search, and careful optimization, you can search databases with unmatched speed and precision.

We’ve only scratched the surface of what’s possible. Continue practicing with LIKE and see what powerful queries you can create.

I hope this guide gave you a comprehensive overview of mastering pattern matching with SQLite LIKE.

References: https://stackoverflow.com/questions/5071601/how-do-i-use-regex-in-a-sqlite-query