Skip to main content

Edit this page

Wildcard Search

Search for partial matches using pattern wildcards. Useful when you need prefix, infix, suffix, or complex pattern matching on indexed terms.

See Setup for the shared dataset used in all examples.

Wildcard characters​

CharacterMeaning
_Match any single character
%Match zero or more characters
\_Literal underscore
\%Literal percent sign

Prefix matching​

Find genres starting with 'sci':

SELECT id, title, genre
FROM movies_idx
WHERE TERM_LIKE(genre, 'sci%')
ORDER BY id;
 id | title                           | genre
----+---------------------------------+--------
  1 | The Matrix                      | sci-fi
  2 | The Matrix Reloaded             | sci-fi
  3 | The Matrix Revolutions          | sci-fi
  7 | Star Trek: The Motion Picture   | sci-fi
  8 | Alien                           | sci-fi

Suffix matching​

Find genres ending with 'dy':

SELECT id, title, genre
FROM movies_idx
WHERE TERM_LIKE(genre, '%dy')
ORDER BY id;
 id | title                         | genre
----+-------------------------------+--------
  6 | Scary Movie                   | comedy
 10 | The Grand Budapest Hotel      | comedy

Infix matching​

Find genres containing 'vent':

SELECT id, title, genre
FROM movies_idx
WHERE TERM_LIKE(genre, '%vent%')
ORDER BY id;
 id | title          | genre
----+----------------+-----------
  4 | Jurassic Park  | adventure

Single character wildcard​

Match genres with exactly one character between 'co' and 'edy':

SELECT id, title, genre
FROM movies_idx
WHERE TERM_LIKE(genre, 'co_edy')
ORDER BY id;
 id | title                         | genre
----+-------------------------------+--------
  6 | Scary Movie                   | comedy
 10 | The Grand Budapest Hotel      | comedy

Complex patterns​

Combine wildcards for more specific matching. Find words matching 'h_____' (h + exactly 5 characters):

SELECT id, title
FROM movies_idx
WHERE TERM_LIKE(description, 'h_____')
ORDER BY id;

Wildcard on text fields​

Search for tokens matching a pattern within full-text columns:

SELECT id, title
FROM movies_idx
WHERE TERM_LIKE(description, 'block%')
ORDER BY id;
 id | title
----+---------------
  4 | Jurassic Park
  6 | Scary Movie

Combine with other search functions​

SELECT id, title, genre
FROM movies_idx
WHERE TERM_LIKE(genre, 'sci%') AND PHRASE(description, 'alien')
ORDER BY id;
 id | title                           | genre
----+---------------------------------+--------
  7 | Star Trek: The Motion Picture   | sci-fi
  8 | Alien                           | sci-fi

See also​