RLIKE character matching - MaxCompute - Alibaba Cloud Documentation Center

RLIKE is an operator provided by MaxCompute SQL based on the PCRE (Perl Compatible Regular Expressions) specification, used for pattern matching and replacement in SQL statements. This topic describes the usage and examples of the RLIKE operator. For the complete list of metacharacters and character classes supported by regular expressions in MaxCompute SQL, see Regular expressions.

Metacharacters

The following table lists the commonly used metacharacters. For a complete list, see Regular expressions.

Metacharacter	Description
`^`	Matches the beginning of a string.
`$`	Matches the end of a string.
`.`	Matches any single character.
`*`	Matches the preceding character or pattern zero or more times.
`+`	Matches the preceding character or pattern one or more times.
`?`	Matches the preceding character or pattern zero or one time. When placed after a quantifier (`*`, `+`, `?`, `{n}`, `{n,}`, or `{n,m}`), switches that quantifier to non-greedy mode. Non-greedy mode matches as few characters as possible; the default greedy mode matches as many as possible.
`{n}` or `{m,n}`	`{n}` matches exactly n times. `{m,n}` matches between m and n times.
`\`	Escape character.
`\d`	Matches any digit (0–9).
`\D`	Matches any non-digit character.

Examples

General pattern matching

Matching the beginning and end of a string

-- Check whether the string aa123bb starts with a. Returns true.
SELECT 'aa123bb' RLIKE '^a';

-- Check whether the string aa123bb starts with a and ends with b. Returns true.
SELECT 'aa123bb' RLIKE '^a.*b$';

-- Check whether the string footerbar contains foo followed by bar. Returns true.
SELECT 'footerbar' RLIKE 'foo(.*?)(bar)';

-- Check whether the string footerbar starts with foo and ends with bar. Returns true.
SELECT 'footerbar' RLIKE '^foo(.*?)(bar)$';

Matching any character

-- Check whether the string cc123bb starts with any character in the range a to d. Returns true.
SELECT 'cc123bb' RLIKE '^[a-d]';

-- Check whether the string 12abc34 starts with 12, ends with 34, and contains at least one character in the range a to d in between. Returns true.
SELECT '12abc34' RLIKE '^12[a-d]+34$';

Escape character matching

The RLIKE operator uses a backslash (\) as the escape character. Therefore, any backslash (\) that appears in a regular expression pattern must be escaped twice.

Example 1
To match the string a+b, where + is a special regex character, it must be escaped. In the regex engine, this is expressed as a\+b. Because an additional layer of escaping is required, the expression that matches the string is a\\+b.
```
SELECT 'a+b' RLIKE 'a\\+b';

-- Results:
+------+
| _c1  |
+------+
| true |
+------+
```
Example 2
To match the backslash character \, which is a special character in the regex engine and must be expressed as \\, an additional layer of escaping requires it to be written as \\\\.
In MaxCompute SQL, a\\b is written in the expression, but a\b is displayed in the output, because MaxCompute applies escaping to the expression.
```
SELECT 'a\\b', 'a\\b' RLIKE 'a\\\b';

-- Results:
+-----+------+
| _c0 | _c1  |
+-----+------+
| a\b | false |
+-----+------+

SELECT 'a\\b', 'a\\b' RLIKE 'a\\\\b';

-- Results:
+-----+------+
| _c0 | _c1  |
+-----+------+
| a\b | true |
+-----+------+
```
Example 3
If the string contains a tab character (TAB), the system stores \t as a single character when reading it. Therefore, in the regex pattern, it is treated as a regular character.
```
SELECT 'a\tb', 'a\tb' RLIKE 'a\tb';

-- Results:
+---------+------+
| _c0     | _c1  |
+---------+------+
| a     b | true |
+---------+------+
```

Example 4

Match strings that contain digits (\d), non-digits (\D), and backreferences (\n).

-- Check whether the string 2025maxcompute starts with a digit character. Returns true.
SELECT '2025maxcompute' RLIKE '^\\\d';

-- Check whether the string maxcompute2025test starts with a non-digit character. Returns true.
SELECT 'maxcompute2025test' RLIKE '^\\\D';

-- Check whether the string alibaba-cloud-MC2025-test contains MC followed by digits. Returns true.
SELECT 'alibaba-cloud-MC2025-test' RLIKE 'MC\\\d';

-- Check whether the string alibaba-cloud-MC2025-test contains MC followed by exactly 4 digits. Returns true.
SELECT 'alibaba-cloud-MC2025-test' RLIKE 'MC\\\d{4}-';

-- Check whether the string contains abcdefdef. Returns true.
-- abc is capture group 1, def is capture group 2, and \2 represents a backreference to the second capture group, which must be escaped again as \\\2. This matches abcdefdef.
SELECT 'mmabcdefdefgg' RLIKE '(abc)(def)\\\2';

Character groups

POSIX character groups

For the complete list of POSIX character classes, see Regular expressions.

Character group	Description	Equivalent
`[[:alnum:]]`	Letters and digits	`[a-zA-Z0-9]`
`[[:alpha:]]`	Letters	`[a-zA-Z]`
`[[:digit:]]`	Digits	`[0-9]`
`[[:lower:]]`	Lowercase letters	`[a-z]`
`[[:space:]]`	Whitespace characters	`[ \t\r\n\v\f]`
`[[:upper:]]`	Uppercase letters	`[A-Z]`

Examples

-- Check whether the string hello123 contains only letters and digits. Returns true.
SELECT 'hello123' RLIKE '^[[:alnum:]]+$';

-- Check whether the string hello_123 contains only letters and digits. Returns false.
SELECT 'hello_123' RLIKE '^[[:alnum:]]+$';

Chinese character groups

For the complete list of Chinese character groups, see Regular expressions.

Character group	Pattern
Double-byte characters (e.g., Chinese characters)	`[^\\\x{00}-\\\x{ff}]`
Chinese characters	`[\\x{4e00}-\\x{9fa5}]`
Chinese punctuation	Chinese punctuation marks do not have a unified encoding range. Use the Unicode code point of each character to match individually.

Examples

The Chinese period('。') corresponds to Unicode code point \x{3002}:

SELECT * FROM VALUES ('你好。'),('nihao!') t(d) WHERE d RLIKE '[\\x{3002}]';

-- Returns:
-- +------------+
-- | d          |
-- +------------+
-- | 你好。      |
-- +------------+

To match a single quotation mark, use the pattern [\x{0027}] (Unicode code point 0027).