Pattern matching
AnalyticDB for PostgreSQL supports three pattern matching approaches inherited from PostgreSQL: the LIKE operator, the SIMILAR TO operator, and Portable Operating System Interface (POSIX) regular expressions. For the full PostgreSQL specification, see Pattern Matching.
LIKE operator
LIKE matches a string against a pattern using wildcard characters.
Syntax
SELECT column1, column2, ...
FROM table
WHERE column LIKE pattern;Parameters
| Parameter | Description |
|---|---|
column | The column whose values to match against the pattern |
pattern | The pattern to match; can include wildcard characters |
Wildcard characters
| Wildcard | Matches |
|---|---|
% | Zero or more characters |
_ | Any single character |
Examples
| Pattern | Description |
|---|---|
column LIKE 'abc%' | Values that start with abc |
column LIKE '%xyz%' | Values that contain xyz |
column LIKE '_bc_' | Four-character values with b and c as the second and third characters |
column LIKE 'a_b_c_' | Six-character values with a, b, and c as the first, third, and fifth characters |
column LIKE 'a%b' | Values that start with a and end with b |
SIMILAR TO operator
SIMILAR TO extends LIKE with regular expression metacharacters. It matches the pattern against the entire string.
Syntax
SELECT column1, column2, ...
FROM table
WHERE column SIMILAR TO pattern;Parameters
| Parameter | Description |
|---|---|
column | The column whose values to match against the pattern |
pattern | The regular expression pattern to match |
Supported metacharacters
SIMILAR TO supports all standard regex metacharacters, including ., *, +, ?, |, (, and ).
Example
-- Match rows where the column contains one or more digits
SELECT * FROM table WHERE column SIMILAR TO '[0-9]+';[0-9]— matches any digit from 0 to 9+— requires the preceding element to appear one or more times
POSIX regular expressions
POSIX regular expressions conform to the POSIX standard and support full regex syntax. Use them with regexp_replace() and regexp_match() to transform or extract substrings.
regexp_replace()
regexp_replace() replaces substrings that match a POSIX regular expression with new text.
Syntax
regexp_replace(string text, pattern text, replacement text [, flags text])Parameters
| Parameter | Description |
|---|---|
string | The original string to match against |
pattern | The regular expression pattern |
replacement | The string to substitute for each match |
flags | Optional flags that control matching behavior (e.g., g for global replace) |
Example
Replace all uppercase letters with their lowercase equivalents:
Sample query
SELECT regexp_replace('Hello, world!', '[A-Z]', lower('\1'), 'g');Return result
hello, world!regexp_match()
regexp_match() extracts substrings that match a regular expression. It returns an array of substrings corresponding to each capture group in the pattern, or an empty string if there is no match.
Syntax
regexp_match(expression text, pattern text [, flags text])Parameters
| Parameter | Description |
|---|---|
expression | The string to match against |
pattern | The regular expression pattern |
flags | Optional flags that control how the pattern is interpreted |
Example
Extract first names from a name column containing full names:
Input data:
| name |
|---|
| John Smith |
| Jane Doe |
Sample query
SELECT regexp_match(name, '^(.*?)[ ](.*?)$') AS first_name
FROM names;Result
| first_name |
|---|
| {John} |
| {Jane} |
The pattern ^(.*?)[ ](.*?)$ splits on the first space. The first capture group (.*?) matches the first name, and regexp_match() returns it as the first element of the array.