Hologres supports standard PostgreSQL syntax and is compatible with PostgreSQL pattern matching. Three approaches to pattern matching are available:
LIKE / NOT LIKE: Simple wildcard matching using
%and_. Best for straightforward string comparisons.SIMILAR TO / NOT SIMILAR TO: SQL standard regular expression matching. More expressive than LIKE, but less powerful than POSIX regular expressions.
POSIX regex operators (
~,~*,!~,!~*): Full POSIX-style regular expression matching. Most powerful option; supports both case-sensitive and case-insensitive matching.
The functions listed below are a subset of PostgreSQL pattern matching functions. For the full reference, see Pattern Matching in the PostgreSQL documentation.
Supported functions
LIKE operators
| Operator | Description | Example | Result |
|---|---|---|---|
LIKE | Returns TRUE if the string matches the pattern; FALSE otherwise. | 'abc' LIKE 'a%' | t |
NOT LIKE | Returns TRUE if the string does not match the pattern; FALSE otherwise. | 'abc' NOT LIKE 'c' | t |
SIMILAR TO operators
| Operator | Description | Example | Result |
|---|---|---|---|
SIMILAR TO | Returns TRUE if the string matches the SQL standard regular expression pattern; FALSE otherwise. | 'abc' SIMILAR TO '%(b|d)%' | t |
NOT SIMILAR TO | Returns TRUE if the string does not match the SQL standard regular expression pattern; FALSE otherwise. | 'abc' NOT SIMILAR TO '(b|c)%' | t |
POSIX regex operators
Hologres uses rlike as the alias for POSIX regular expression matching. The following operators are supported:
| Operator | Description | Example | Result |
|---|---|---|---|
~ | Returns TRUE if the string matches the regular expression. Case-sensitive. | 'abc' ~ '(b|d)' | t |
~* | Returns TRUE if the string matches the regular expression. Case-insensitive. | 'abc' ~* '(B|D)' | t |
!~ | Returns TRUE if the string does not match the regular expression. Case-sensitive. | 'abc' !~ '(b|d)' | f |
!~* | Returns TRUE if the string does not match the regular expression. Case-insensitive. | 'abc' !~* '(B|D)' | f |