AnalyticDB for PostgreSQL is compatible with PostgreSQL pattern matching. This topic describes the pattern matching operators, regular expressions, and functions that are supported by AnalyticDB for PostgreSQL and provides examples on how to use the operators, regular expressions, and functions.
PostgreSQL provides the following approaches to perform pattern matching: LIKE operator, SIMILAR TO operator, and Portable Operating System Interface (POSIX) regular expressions. For information about how to perform PostgreSQL pattern matching, see Pattern Matching.
LIKE operator
LIKE is an SQL keyword that can be used for pattern matching within a SELECT statement. LIKE pattern matching refers to matching the entire or a part of a string by using wildcard characters.
Syntax
SELECT column1, column2, ...
FROM table
WHERE column LIKE pattern;Parameters:
column: the name of the column whose values you want to match against the pattern.
pattern: the pattern that is used for matching, which can contain wildcard characters.
The following wildcard characters are supported:
%: zero or multiple characters.
_: any single character.
Examples
The following table provides examples on how to use the LIKE operator.
Example | SQL statement | Description |
Example 1 | SELECT * FROM table WHERE column LIKE 'abc%' | Queries all rows whose values in the specified column start with abc. |
Example 2 | SELECT * FROM table WHERE column LIKE '%xyz%' | Queries all rows whose values in the specified column contain xyz. |
Example 3 | SELECT * FROM table WHERE column LIKE '_bc_' | Queries all rows whose values in the specified column are four characters in length and contain b and c as the second and third characters. |
Example 4 | SELECT * FROM table WHERE column LIKE 'a_b_c_' | Queries all rows whose values in the specified column are six characters in length and contain a, b, and c as the first, third, and fifth characters. |
Example 5 | SELECT * FROM table WHERE column LIKE 'a%b' | Queries all rows whose values in the specified column start with a and end with b. |
SIMILAR TO operator
SIMILAR TO is a PostgreSQL keyword that allows you to perform pattern matching by using regular expressions within a SELECT statement.
Syntax
SELECT column1, column2, ...
FROM table
WHERE column SIMILAR TO pattern;Parameters:
column: the name of the column whose values you want to match against the pattern.
pattern: the regular expression pattern that is used for matching.
Example
The SIMILAR TO operator supports all regular expression metacharacters and operators, such as ., *, +, ?, |, (, and ). Execute the following statement to query all rows that contain digits:
SELECT * FROM table WHERE column SIMILAR TO '[0-9]+';Parameters:
[0-9]: matches any digit from 0 to 9.
+: specifies that the preceding characters can be repeated once or multiple times.
POSIX regular expressions
POSIX regular expressions are regular expressions that conform to the POSIX standard. You can use the following functions together with POSIX regular expressions for pattern matching: regexp_replace() and regexp_match().
regexp_replace()
The regexp_replace() function can be used to replace substrings that match POSIX regular expression patterns with new text.
Syntax
regexp_replace(string text, pattern text, replacement text [, flags text]);Parameters:
string: the original string that you want to match against the pattern.
pattern: the regular expression pattern that is used for matching.
replacement: the new string that is used to replace substrings of the original string that match the pattern.
flags: the regular expression flags that are used to control pattern matching. This parameter is optional.
Example
Execute the following statement to replace all uppercase letters in the Hello, world! string with the corresponding lowercase letters.
Sample statement
SELECT regexp_replace('Hello, world!', '[A-Z]', lower('\1'), 'g');Sample result
hello, world!regexp_match()
The regexp_match() function is a string manipulation function in PostgreSQL that can be used to extract parts of a string that match a regular expression. This function returns an array. If matches are found, this function returns an array that contains substrings corresponding to each capture group in the pattern. Otherwise, this function returns an empty string.
Syntax
regexp_match(expression, pattern [, flags]);Parameters:
expression: the string that you want to match against the pattern.
pattern: the regular expression pattern that is used for matching.
flags: the string flags that control how the pattern is interpreted. This parameter is optional.
Example
In this example, a column named name that contains the following data is used:
name |
John Smith |
Jane Doe |
Execute the following statement to extract the first names from a list of full names stored in the name column. In this statement, the regexp_match() function is invoked.
Sample statement
SELECT regexp_match(name, '^(.*?)[ ](.*?)$') AS first_name
FROM names;Sample result
first_name |
{John} |
{Jane} |