Pattern matching YSQL

Learn how to search text using pattern matching

This page documents the preview version (v2.21). Preview includes features under active development and is for development and testing only. For production, use the stable version (v2024.1). To learn more, see Versioning.

The LIKE operator is a basic pattern-matching operator that emulates wildcard-like matching similar to many *nix shells. Pattern matching can be done either using % (percent) to match any sequence of characters, or _(underscore) to match any single character.

Setup

Setup

To set up a local universe, refer to Set up a local YugabyteDB universe.

Setup

To set up a cluster, refer to Set up a YugabyteDB Aeon cluster.

Setup

To set up a universe, refer to Set up a YugabyteDB Anywhere universe.

Create the following table:

CREATE TABLE IF NOT EXISTS words (
    id SERIAL,
    word TEXT NOT NULL,
    PRIMARY KEY(id)
);

Load some sample words into the table as follows:

INSERT INTO words(word) VALUES
  ('camp'),('carousel'),('cartel'),('carpet'),('carnivore'),('cartoon'),('carry'),('capsule'),
  ('corsica'),('medica'),('azteca'),('republica'),('chronica'),('orca'),('cathodically'),('capably'),
  ('cot'),('cat'),('cut'),('cwt'),('cit'),('cit'),('captainly'),('callously'),('career'),('calculate'),
  ('lychees'),('deer'),('peer'),('seer'),('breeze'),('green'),('teen'),('casually');

Suffix matching

Add % to the end of a pattern to match any string that completes the given pattern. For example, to get all the words starting with ca, execute the following:

SELECT word FROM words WHERE word LIKE 'ca%' limit 5;

      word
--------------
 carnivore
 camp
 capably
 cathodically
 cartoon

Prefix matching

Add % to the beginning of a pattern to match any string that ends in the given pattern. For example, to get words ending with ca, execute the following:

SELECT word FROM words WHERE word LIKE '%ca' limit 5;

   word
-----------
 azteca
 chronica
 republica
 corsica
 medica

Infix matching

You can also use % to match any sequence of text between a given pattern. For example, to get all words starting with ca and ending in ly, execute the following:

SELECT word FROM words WHERE word LIKE 'ca%ly' limit 5;

     word
--------------
 capably
 cathodically
 casually
 captainly
 callously

Case insensitive matching

The LIKE operator performs case-sensitive matching. For example, if you change the pattern to uppercase, you may not get the same results.

SELECT word FROM words WHERE word LIKE 'C_T' limit 5;

 word
------
(0 rows)

To support case-insensitive matching, use the ILIKE operator.

SELECT word FROM words WHERE word ILIKE 'C_T' limit 5;

 word
------
 cit
 cot
 cut
 cat
 cit

Regex matching

Use the SIMILAR TO operator to match patterns using the SQL standard's definition of a regular expression. SQL regular expressions are a cross between LIKE notation and common (POSIX) regular expression notation.

For example, to find all words that have e occurring three or more times consecutively, do the following:

SELECT word FROM words WHERE word SIMILAR TO '%e{2,}%' ;

  word
---------
 peer
 green
 seer
 lychees
 deer
 teen
 breeze
 career

SIMILAR TO supports the following pattern-matching meta-characters:

  • | denotes alternation (either of two alternatives).
  • * denotes repetition of the previous item zero or more times.
  • + denotes repetition of the previous item one or more times.
  • ? denotes repetition of the previous item zero or one time.
  • {m} denotes repetition of the previous item exactly m times.
  • {m,} denotes repetition of the previous item m or more times.
  • {m,n} denotes repetition of the previous item at least m and not more than n times.

Use parentheses () to group items into a single logical item. A bracket expression [...] specifies a character class, just as in POSIX regular expressions.

Single character matching

Use _(underscore) to match any single character. To get all the 3 letter words that start with c and end in t, execute the following:

SELECT word FROM words WHERE word LIKE 'c_t' limit 5;

 word
------
 cit
 cot
 cut
 cat
 cit

Learn more