Pattern Matching Operators

Operators for string pattern matching and regular expressions.

like - Pattern Matching (Case-Sensitive)

Matches strings using SQL LIKE patterns.

// Starts with
{ where: { email: { like: '%@gmail.com' } } }
// SQL: WHERE "email" LIKE '%@gmail.com'

// Contains
{ where: { name: { like: '%john%' } } }
// SQL: WHERE "name" LIKE '%john%'

// Ends with
{ where: { filename: { like: '%.pdf' } } }
// SQL: WHERE "filename" LIKE '%.pdf'

// Single character wildcard
{ where: { code: { like: 'A_B' } } }  // Matches 'A1B', 'AXB', etc.
// SQL: WHERE "code" LIKE 'A_B'

Pattern Characters:

• % - Matches any sequence of characters (including empty)
• _ - Matches exactly one character

nlike - Not Like

{ where: { email: { nlike: '%@test.com' } } }
// SQL: WHERE "email" NOT LIKE '%@test.com'

ilike - Case-Insensitive Pattern Matching

PostgreSQL-specific case-insensitive LIKE.

{ where: { name: { ilike: '%john%' } } }
// SQL: WHERE "name" ILIKE '%john%'
// Matches: 'John', 'JOHN', 'john', 'JoHn'

{ where: { email: { ilike: '%@GMAIL.COM' } } }
// Matches: 'user@gmail.com', 'USER@Gmail.Com'

nilike - Not ILike

{ where: { email: { nilike: '%@example%' } } }
// SQL: WHERE NOT ("email" ILIKE '%@example%')

regexp - Regular Expression (Case-Sensitive)

PostgreSQL POSIX regex matching.

// Starts with letter
{ where: { code: { regexp: '^[A-Z]' } } }
// SQL: WHERE "code" ~ '^[A-Z]'

// Email pattern
{ where: { email: { regexp: '^[a-z]+@[a-z]+\\.[a-z]+$' } } }
// SQL: WHERE "email" ~ '^[a-z]+@[a-z]+\.[a-z]+$'

// Phone number pattern
{ where: { phone: { regexp: '^\\+?[0-9]{10,15}$' } } }

NOTE

Escape backslashes in TypeScript strings: \\d for regex \d.

iregexp - Case-Insensitive Regular Expression

{ where: { name: { iregexp: '^john' } } }
// SQL: WHERE "name" ~* '^john'
// Matches: 'John Doe', 'JOHN SMITH', 'john'

Summary

Operator	SQL	Case	Description
like	LIKE	Sensitive	Pattern with wildcards
nlike	NOT LIKE	Sensitive	Negative pattern
ilike	ILIKE	Insensitive	PostgreSQL only
nilike	NOT ILIKE	Insensitive	PostgreSQL only
regexp	~	Sensitive	POSIX regex match
iregexp	~*	Insensitive	POSIX regex match