Skip to main content
SereneDBSereneDB
897Contact Us

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':

SQL
SELECT id, title, genreFROM movies_idxWHERE genre @@ ts_like('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':

SQL
SELECT id, title, genreFROM movies_idxWHERE genre @@ ts_like('%dy')ORDER BY id;
 id | title                    | genre
----+--------------------------+--------
  6 | Scary Movie              | comedy
 10 | The Grand Budapest Hotel | comedy

Infix matching​

Find genres containing 'vent':

SQL
SELECT id, title, genreFROM movies_idxWHERE genre @@ ts_like('%vent%')ORDER BY id;
 id | title         | genre
----+---------------+-----------
  4 | Jurassic Park | adventure

Single character wildcard​

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

SQL
SELECT id, title, genreFROM movies_idxWHERE genre @@ ts_like('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):

SQL
SELECT id, titleFROM movies_idxWHERE description @@ ts_like('h_____')ORDER BY id;
 id | title
----+--------------
  1 | The Matrix
  9 | Café Society

Wildcard on text fields​

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

SQL
SELECT id, titleFROM movies_idxWHERE description @@ ts_like('block%')ORDER BY id;
 id | title
----+---------------
  4 | Jurassic Park
  6 | Scary Movie

Combine with other search functions​

SQL
SELECT id, title, genreFROM movies_idxWHERE genre @@ ts_like('sci%') AND description @@ ts_phrase('alien')ORDER BY id;
 id | title                         | genre
----+-------------------------------+--------
  7 | Star Trek: The Motion Picture | sci-fi

See also​

Edit this page ↗