This topic describes the regular functions that are supported for AnalyticDB for MySQL clusters.

Prerequisites

The minor engine version of the AnalyticDB for MySQL cluster is 3.1.5.10 or later.
Note For more information about how to view the minor engine version of a cluster, see How can I view the version of an AnalyticDB for MySQL cluster?

REGEXP_INSTR

regexp_instr(source, pattern[, position[, occurrence[, option]]] )
  • Description: This function matches a regular expression against a string and returns an integer that indicates the start or end position of the matched substring. By default, the position parameter is set to 1, which indicates that the match starts from the first character of the specified string. By default, the occurrence parameter is set to 1, which indicates that the first matched substring is returned. By default, the option parameter is set to 0, which indicates that the start position of the matched substring is returned. If you set option to 1, the next character position of the matched substring is returned.
  • Parameters:
    • source: the string to be searched. Data type: VARCHAR.
    • pattern: the regular expression.
    • position: the start position to match the specified string. Default value: 1. Data type: BIGINT.
    • occurrence: the sequence number of occurrence for the matched substring to return. Default value: 1. Data type: BIGINT.
    • option: specifies whether to return the offset of the first character of the match or the offset of the first character following the end of the match. Valid values: 0 and 1. Default value: 0. Data type: BIGINT.
  • Return value: The return value is of the BIGINT type. If no matches are found, a value of 0 is returned.
  • Examples:
    • Example 1
      SELECT REGEXP_INSTR('dog cat dog', 'dog') as res;
      The following information is returned: 
      +-----+
| res |
+-----+
|   1 |
+-----+
    • Example 2
      SELECT REGEXP_INSTR('dog cat dog', 'dog', 1, 2) as res;
      The following information is returned: 
      +-----+
| res |
+-----+
|   9 |
+-----+
    • Example 3
      SELECT REGEXP_INSTR('dog cat dog', 'dog', 1, 1, 1) as res;
      The following information is returned: 
      +-----+
| res |
+-----+
|   4 |
+-----+

REGEXP_MATCHES

regexp_matches(source, pattern[, flag])
  • Description: This function matches a regular expression against a string and returns an array of matched substrings. If the specified regular expression contains the g flag, all the matched substrings are returned in the array. Otherwise, only the first matched substring is returned in the array. If the value of the pattern parameter contains subexpressions, the matched substrings of the subexpressions are returned in the array. Otherwise, the matched substring of the specified regular expression is returned in the array.
  • Parameters:
    • source: the string to be searched. Data type: VARCHAR.
    • pattern: the regular expression.
    • flag: one or more characters that control the behavior of the function. Data type: VARCHAR.
  • Return value: The return value is of the ARRAY(ARRAY(VARCHAR)) type. If no matches are found, an empty array is returned.
  • Examples:
    • Example 1
      SELECT regexp_matches('foobarbequebaz', '(bar)(beque)');
      The following information is returned: 
      +---------------------+
| regexp_matches      |
+---------------------+
| [["bar","beque"]]   |
    • Example 2
       SELECT regexp_matches('foobarbequebaz', 'barbeque');
      The following information is returned: 
      +---------------------+
| regexp_matches      |
+---------------------+
| [["barbeque"]]      |
    • Example 3
      SELECT regexp_matches('foobarbequebazilbarfbonk', '(b[^b]+)(b[^b]+)', 'g');
      The following information is returned: 
      +------------------------------------------+
| regexp_matches                           |
+------------------------------------------+
| [["bar","beque"], ["bazil","barf"]]      |

REGEXP_REPLACE

regexp_replace(source, pattern, replacement[, position[, occurrence]])
  • Description: This function matches a regular expression against a string and replaces the matched substrings. By default, the position parameter is set to 1, which indicates that the match and replacement start from the first character of the specified string. By default, the occurrence parameter is set to 0, which indicates that all the matched substrings are replaced.
  • Parameters:
    • source: the string to be searched. Data type: VARCHAR.
    • pattern: the regular expression.
    • replacement: the string that is used to replace the matches. Data type: VARCHAR.
    • position: the start position to match and replace the matches. Default value: 1. Data type: BIGINT.
    • occurrence: the sequence number of occurrence for the matched and replaced substring to return. Default value: 0. Data type: BIGINT.
  • Return value: The return value is of the VARCHAR type. If no matches are found, the specified string is returned.
  • Examples:
    • Example 1
      SELECT REGEXP_REPLACE('abc def ghi', '[a-z]+', 'X') as res;
      The following information is returned: 
      +-------+
| res   |
+-------+
| X X X |
+-------+
    • Example 2
      SELECT REGEXP_REPLACE('abc def ghi', '[a-z]+', 'X', 1, 3) as res;
      The following information is returned: 
      +-----------+
| res       |
+-----------+
| abc def X |
+-----------+

REGEXP_SUBSTR

regexp_substr(source, pattern[, position[, occurrence]])
  • Description: This function matches a regular expression against a string and returns the matched substrings. By default, the position parameter is set to 1, which indicates that the match starts from the first character of the specified string. By default, the occurrence parameter is set to 1, which indicates that the first matched substring is returned.
  • Parameters:
    • source: the string to be searched. Data type: VARCHAR.
    • pattern: the regular expression.
    • position: the start position to match the specified string. Default value: 1. Data type: BIGINT.
    • occurrence: the sequence number of occurrence for the matched substring to return. Default value: 1. Data type: BIGINT.
  • Return value: The return value is of the VARCHAR type. If no matches are found, NULL is returned.
  • Examples:
    • Example 1
      SELECT REGEXP_SUBSTR('abc def ghi', '[a-z]+', 1, 3) as res;
      The following information is returned: 
      +------+
| res  |
+------+
| ghi  |
+------+
    • Example 2
      SELECT REGEXP_SUBSTR('abc def ghi', '[a-z]+') as res;
      The following information is returned: 
      +------+
| res  |
+------+
| abc  |
+------+