---
title: "LIKE operator"
description: "The LIKE operator performs pattern matching on string values, using % as a wildcard for any number of characters and _ for a single character."
canonical: "https://motherduck.com/glossary/like-operator/"
related:
  - title: "DuckDB SQL | MotherDuck Docs"
    url: "https://motherduck.com/docs/sql-reference/duckdb-sql-reference/"
  - title: "Text Search in MotherDuck | MotherDuck Docs"
    url: "https://motherduck.com/docs/key-tasks/ai-and-motherduck/text-search-in-motherduck/"
  - title: "From Data Lake to Lakehouse: Can DuckDB be the best portable catalog?"
    url: "https://motherduck.com/blog/from-data-lake-to-lakehouse-duckdb-portable-catalog/"
---

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

```sql
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"
```

<glossary-callout guide="duckdb-cheatsheet-full" />

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

```sql
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:

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

## DuckDB's related pattern operators
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.