Text and Pattern Functions and Operators

This document covers PostgreSQL text processing, pattern matching, and regular expression functions and operators available in this library.

📖 See also: Common Use Cases and Examples for practical text processing and regular expression examples

Text and Pattern Operators

⚠️ Important: Some PostgreSQL operators have multiple meanings depending on the data types involved. This library provides specific DQL function names to avoid conflicts:

Usage Guidelines:

• Text: Use REGEXP, IREGEXP for pattern matching
• Boolean operators: All operators return boolean values and should be used with = TRUE or = FALSE in DQL

Text and Pattern Operators

Regular Expression Functions

Text Processing Functions

Full-Text Search Functions

Fuzzy String Matching Functions (fuzzystrmatch extension)

⚠️ Note: These functions require the PostgreSQL fuzzystrmatch extension to be installed and enabled in your database.

Trigram Similarity Functions (pg_trgm extension)

⚠️ Note: These functions and operators require the PostgreSQL pg_trgm extension to be installed and enabled.

Similarity functions

Similarity threshold operators

Distance operators

Hashing and Checksum Functions

Usage Examples

-- REGEXP_LIKE with a real-world email pattern — POSIX syntax, not SQL LIKE syntax
SELECT e FROM Entity e WHERE REGEXP_LIKE(e.email, '^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$') = TRUE

-- REGEXP_MATCH returns an array of capture groups (one element per group)
SELECT REGEXP_MATCH(e.text, '([0-9]{4})-([0-9]{2})-([0-9]{2})') as date_parts FROM Entity e

-- Full-text search: combine TO_TSVECTOR + TO_TSQUERY; must use = TRUE in DQL WHERE clauses
SELECT e FROM Entity e WHERE TSMATCH(TO_TSVECTOR(e.content), TO_TSQUERY('search & terms')) = TRUE

-- TS_RANK: sort by relevance score — smaller values are less relevant
SELECT e, TS_RANK(TO_TSVECTOR(e.content), TO_TSQUERY('search')) as rank FROM Entity e ORDER BY rank DESC

-- STRING_AGG with separator — requires GROUP BY
SELECT e.category, STRING_AGG(e.name, ', ' ORDER BY e.name) as names FROM Entity e GROUP BY e.category

💡 Tips for Usage:

1. Boolean operators should be used with = TRUE or = FALSE in DQL
2. Regular expressions use PostgreSQL's POSIX regular expression syntax
3. Full-text search requires proper text search configuration and indexes
4. ILIKE provides case-insensitive pattern matching similar to LIKE
5. UNACCENT requires the unaccent extension to be installed in PostgreSQL
6. String aggregation with STRING_AGG allows custom separators and ordering
7. Fuzzy string matching functions require the fuzzystrmatch extension to be installed in PostgreSQL