Pattern matching YSQL

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

• Local
• YB Managed
• YB Anywhere

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

• Understand GIN indexes
• Advanced fuzzy matching in YugabyteDB
• Optimizing LIKE/ILIKE with indexes