Pattern matching operators

This page describes the available operators to assist with performing pattern matching. For operators using regular expressions (regex in the syntax), QuestDB uses Java regular expression implementation.

:::note VARCHAR and STRING data types

QuestDB supports two types of string data: VARCHAR and STRING. Most users should use VARCHAR as it is more efficient. See VARCHAR vs STRING for more information.

Functions described in this page work with both types.

:::

~ (match) and !~ (does not match)

• (string) ~ (regex) - returns true if the string value matches a regular expression, regex, otherwise returns false (case sensitive match).
• (string) !~ (regex) - returns true if the string value fails to match a regular expression, regex, otherwise returns false (case sensitive match).

Arguments

• string is an expression that evaluates to the string data type.
• regex is any regular expression pattern.

Return value

Return value type is boolean.

LIKE/ILIKE

• (string) LIKE (pattern) - returns true if the string value matches pattern, otherwise returns false (case sensitive match).
• (string) ILIKE (pattern) - returns true if the string value matches pattern, otherwise returns false (case insensitive match).

Arguments

• string is an expression that evaluates to the string data type.
• pattern is a pattern which can contain wildcards like _ and %.

Return value

Return value type is boolean.

Description

If the pattern doesn't contain wildcards, then the pattern represents the string itself.

The wildcards which can be used in pattern are interpreted as follows:

• _ - matches any single character.
• % - matches any sequence of zero or more characters.

Wildcards can be used as follows:

SELECT 'quest' LIKE 'quest' ;
-- Returns true
SELECT 'quest' LIKE 'ques_';
-- Returns true
SELECT 'quest' LIKE 'que%';
-- Returns true
SELECT 'quest' LIKE '_ues_';
-- Returns true
SELECT 'quest' LIKE 'q_'
-- Returns false

ILIKE performs a case insensitive match as follows:

SELECT 'quest' ILIKE 'QUEST';
-- Returns true
SELECT 'qUeSt' ILIKE 'QUEST';
-- Returns true
SELECT 'quest' ILIKE 'QUE%';
-- Returns true
SELECT 'QUEST' ILIKE '_ues_';
-- Returns true

Examples

LIKE

SELECT * FROM trades
WHERE symbol LIKE '%-USDT'
LATEST ON timestamp PARTITION BY symbol;

ILIKE

SELECT * FROM trades
WHERE symbol ILIKE '%-usdt'
LATEST ON timestamp PARTITION BY symbol;

regexp_replace

regexp_replace (string1, regex , string2 ) - provides substitution of new text for substrings that match regular expression patterns.

Arguments:

• string1 is a source string value to be manipulated.
• regex is a regular expression pattern.
• string2 is any string value to replace part or the whole of the source value.

Return value

Return value type is string. The source string is returned unchanged if there is no match to the pattern. If there is a match, the source string is returned with the replacement string substituted for the matching substring.

Examples:

SELECT regexp_replace('MYSQL is a great database', '^(\S*)', 'QuestDB');

QuestDB is a great database