The Database Inside Your Lakehouse: A DuckLake Architecture Deep DiveLivestream July 9

START FREE
← Back to Glossary

LIKE operator

The LIKE operator performs pattern matching on string values, using % as a wildcard for any number of characters and _ for a single character.

Overview

LIKE tests whether a string matches a pattern containing two special wildcard characters: % (matches zero or more characters) and _ (matches exactly one character). It's the standard ANSI SQL mechanism for simple substring and pattern-based filtering.

Copy code
SELECT name FROM products WHERE name LIKE 'Duck%';     -- starts with "Duck"
SELECT name FROM products WHERE name LIKE '%SQL%';      -- contains "SQL" anywhere
SELECT name FROM products WHERE name LIKE '_uck';       -- 4 letters, ending in "uck"

Case sensitivity

LIKE is case-sensitive by default in DuckDB and most databases. For case-insensitive matching, DuckDB provides ILIKE:

Copy code
SELECT name FROM products WHERE name ILIKE '%duck%';   -- matches "Duck", "DUCK", "duck"

Escaping wildcards

To match a literal % or _ character, use the ESCAPE clause to designate an escape character:

Copy code
SELECT code FROM promotions WHERE code LIKE '50\%off' ESCAPE '\';

Beyond LIKE/ILIKE, DuckDB supports:

  • SIMILAR TO, which uses full SQL regular expression syntax rather than just %/_.
  • GLOB, which uses Unix shell-style globbing (* and ?), commonly seen when matching file paths, e.g. read_csv('data/*.csv').
  • regexp_matches() / regexp_replace(), for full regular expression matching and substitution beyond what LIKE and SIMILAR TO support.

LIKE is the right tool for simple prefix/suffix/substring checks; reach for SIMILAR TO or regexp_matches() when you need genuine regular expressions (character classes, alternation, quantifiers).

Performance

A LIKE pattern with a fixed prefix (e.g., 'Duck%') can often use an index or sorted-scan optimization; a pattern starting with % (e.g., '%duck%') generally requires scanning every row, since there's no fixed starting point to seek to.

Related terms

Regular expressions in SQL →

SQL engines support regular expressions through functions and operators like REGEXP_MATCHES, REGEXP_REPLACE, and SIMILAR TO for pattern-based text matching and extraction.

Glob pattern →

A glob pattern is a wildcard-based string pattern, using characters like `*` and `?`, used to match multiple filenames or paths at once instead of listing them individually.

IN operator →

The IN operator tests whether a value matches any value in a given list or subquery, providing a concise alternative to multiple OR conditions.

String functions →

String functions manipulate text values in SQL — concatenating, trimming, searching, splitting, and reformatting strings as part of a query.

ROW_NUMBER →

ROW_NUMBER() is a SQL window function that assigns a unique, sequential integer to each row within a partition based on a specified order, with no ties.

CASE expression →

The CASE expression evaluates a list of conditions and returns a corresponding value for the first one that's true, functioning as SQL's if/else logic inside a query.

FAQS

LIKE is case-sensitive, while ILIKE performs the same wildcard matching case-insensitively.

Use the ESCAPE clause to designate an escape character, then prefix the literal % or _ with it, e.g. LIKE '50%off' ESCAPE ''.

Use LIKE for simple prefix, suffix, or substring checks with % and _; use SIMILAR TO or regexp_matches() when you need real regular expression features like character classes or alternation.