Pattern Matching

Regex Matching

Use ~ to match a value against a regular expression, and !~ to negate the match:

message ~ "error.*timeout"

path !~ "^/health"

Operator	Meaning
~	Value matches regex pattern
!~	Value does not match regex pattern

Regex Pattern Syntax

The regex syntax depends on the target backend:

• ClickHouse — uses match() function (RE2 syntax)

• PostgreSQL — uses ~ and !~ operators (POSIX regex)

• StarRocks — uses regexp function

• In-memory matcher — uses the host language’s regex engine:

  • Go: stdlib regexp (RE2-based, always available)
  • Python: google-re2 via the optional [re2] extra (required for ~ and LIKE operators; otherwise raises ERR_RE2_MISSING)
  • JavaScript: native RegExp (PCRE-ish — supports backreferences and lookahead, but not RE2-safe against catastrophic backtracking)

  Portable patterns should avoid backreferences and lookahead. A pattern using either is accepted in JS but rejected in Python/Go.

Quote the pattern when it contains spaces or special characters:

message ~ "connection (refused|reset)"

host ~ "^prod-[0-9]+"

Regex Flags

You can modify regex behavior using inline flags at the start of your pattern with the (?...) syntax. FlyQL supports three flags that work across all backends:

(?i) — Case-Insensitive

Makes the pattern match regardless of letter case:

message ~ "(?i)error"

level ~ "(?i)^(warn|error|fatal)$"

(?i)error matches “Error”, “ERROR”, “error”, etc.

(?m) — Multiline

Changes ^ and $ to match the start and end of each line instead of the entire string:

log ~ "(?m)^ERROR"

Without (?m), ^ERROR only matches if the entire string starts with “ERROR”. With (?m), it also matches “INFO first\nERROR second” because “ERROR” appears at the start of a line.

(?s) — Dotall

Makes . match any character including newlines. By default, . does not match \n:

log ~ "(?s)start.*end"

Without (?s), start.*end does not match “start\nend” because . won’t cross the newline. With (?s), it matches.

Combining Flags

Flags can be combined in a single group:

log ~ "(?im)^ERROR"

log ~ "(?ims)^START.*END$"

Backend Support

All three flags work across every FlyQL backend:

Backend	(?i)	(?m)	(?s)
ClickHouse	Yes	Yes	Yes
PostgreSQL	Yes	Yes	Yes
StarRocks	Yes	Yes	Yes
Go matcher	Yes	Yes	Yes
Python matcher	Yes	Yes	Yes
JavaScript matcher	Yes	Yes	Yes

Combine case-insensitive matching with negation to exclude patterns regardless of case:

message ~ "(?i)error" and message !~ "(?i)warning"

LIKE Matching

Use like and ilike for SQL-style pattern matching with wildcards:

host like 'prod%'

service like 'api-%'

message ilike '%Error%'

Operator	Meaning
like	Value matches pattern (case-sensitive)
not like	Value does not match pattern
ilike	Value matches pattern (case-insensitive)
not ilike	Value does not match pattern (case-insensitive)

Wildcards

Two wildcard characters are available in like / ilike patterns:

Wildcard	Meaning	Example	Matches
%	Any sequence of characters (including empty)	host like 'prod%'	prod, prod-1, production
_	Exactly one character	code like 'E__'	E01, Exx, but not E1 or Error

Wildcards can appear anywhere in the pattern — start, middle, end, or combined:

message like '%timeout%'

host like 'web-_-prod'

path like '/api/%/status'

Escaping Wildcards

To match a literal % or _, escape it with a backslash:

value like '100\%'

col like 'foo\_bar'

100\% matches the literal string “100%”, not “100” followed by anything.

Case-Insensitive Matching

Use ilike for case-insensitive pattern matching:

message ilike '%error%'

host not ilike 'TEST%'

LIKE vs. Regex

Use case	Syntax	Example
Prefix match	key like 'prefix%'	host like 'prod%'
Contains	key like '%word%'	msg like '%error%'
Complex pattern	key ~ "pattern"	host ~ "^prod-[0-9]+"

Use like for simple wildcard matching. Use regex (~) when you need character classes, alternation, or other advanced patterns.

Backend Support

All SQL backends (ClickHouse, PostgreSQL, StarRocks) emit native LIKE / ILIKE / NOT LIKE / NOT ILIKE SQL operators. The in-memory matcher converts the pattern to a regex internally.

Note

StarRocks ILIKE requires version 2.5 or later.

Examples

message ~ "error|fail"

path ~ "^/api/v2"

path !~ "^/health"

host like 'prod%'

host like 'prod%' and message ~ "timeout.*" and path !~ "^/health"