Rlike character matching

General rule matching

• Match the beginning and end of a string

  -- Match whether the beginning of the string aa123bb is a. true is returned.
  SELECT 'aa123bb' RLIKE '^a';
  
  -- Match the beginning of the string aa123bb with a and the end of the string aa123bb with b. true is returned.
  SELECT 'aa123bb' RLIKE '^a.*b$';
  
  -- Match whether the string footerbar contains foo and bar in sequence. true is returned.
  SELECT 'footerbar' RLIKE 'foo(.*?)(bar)';
  
  -- Match whether the string footerbar begins with foo and ends with bar. true is returned.
  SELECT 'footerbar' RLIKE '^foo(.*?)(bar)$';

• Match any character

  -- Match whether the beginning of the string cc123bb is any character from a to d. true is returned.
  SELECT 'cc123bb' RLIKE '^[a-d]';
  
  -- Match whether the string 12abc34 begins with 12, ends with 34, and contains at least one character from a to d in the middle. true is returned.
  SELECT '12abc34' RLIKE '^12[a-d]+34$';

Escape character matching

The RLIKE operator supports the backslash \ as an escape character. Therefore, all \ characters in the pattern of a regular expression must be escaped twice.

• Example 1

  The regular expression needs to match the string a+b, where + is a special character in regular expressions. Therefore, it must be expressed as an escape character. In the regular expression engine, it is expressed as a\+b. Because another layer of escaping is required, the expression that can match this string is a\\+b.

  SELECT 'a+b' RLIKE 'a\\+b';
  
  --The following result is returned:
  +------+
  | _c1  |
  +------+
  | true |
  +------+

• Example 2

  To match the character \, in the regular expression engine \ is a special character, so it needs to be represented as \\, and because another layer of escaping is required, it is written as \\\\.

  Note

  In MaxCompute SQL, a\\b is written, but a\b is displayed in the output. This is because MaxCompute escapes the expression.

  SELECT 'a\\b', 'a\\b' RLIKE 'a\\\b';
  
  -- The following result is returned:
  +-----+------+
  | _c0 | _c1  |
  +-----+------+
  | a\b | false |
  +-----+------+
  
  SELECT 'a\\b', 'a\\b' RLIKE 'a\\\\b';
  
  -- The following result is returned:
  +-----+------+
  | _c0 | _c1  |
  +-----+------+
  | a\b | true |
  +-----+------+

• Example 3

  If a string contains a tab character (TAB), the system has already stored \t as a single character when it reads these two characters. Therefore, it is also a regular character in the pattern of the regular expression.

  SELECT 'a\tb', 'a\tb' RLIKE 'a\tb';
  
  -- The following result is returned:
  +---------+------+
  | _c0     | _c1  |
  +---------+------+
  | a     b | true |
  +---------+------+

• Example 4

  Match strings that contain digits \d, non-digit characters \D, and back references \n.

  --Match whether the beginning of the string 2025maxcompute is a digit. true is returned.
  SELECT '2025maxcompute' RLIKE '^\\\d';
  
  --Match whether the beginning of the string maxcompute2025test is a non-digit character. true is returned.
  SELECT 'maxcompute2025test' RLIKE '^\\\D';
  
  --Match whether the string alibaba-cloud-MC2025-test contains the combination of MC and digits. true is returned.
  SELECT 'alibaba-cloud-MC2025-test' RLIKE 'MC\\\d';
  
  -- Match whether the string alibaba-cloud-MC2025-test contains the combination of MC and four digits. true is returned.
  SELECT 'alibaba-cloud-MC2025-test' RLIKE 'MC\\\d{4}-';
  
  -- Match whether the string contains abcdefdef. true is returned.
  -- abc is capturing group 1, def is capturing group 2, \2 represents repeated matching of the second capturing group, which needs to be escaped again as \\\2. That is, abcdefdef.
  SELECT 'mmabcdefdefgg' RLIKE '(abc)(def)\\\2';

Posix character groups

Chinese character groups

Examples

For example, the regular expression for the period (。) is [\\x{3002}].

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

-- The following result is returned:
+------------+
| d          |
+------------+
| Hello.     |
+------------+