MaxCompute:Regular expressions

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';

• 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

  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 |
  +---------+------+