All Products
Search

This Product

    MaxCompute:REGEXP_COUNT

    Document Center

    MaxCompute:REGEXP_COUNT

    Last Updated:Oct 23, 2025

    The REGEXP_COUNT function calculates the number of times a pattern appears in a string, starting from a specified position.

    Syntax

    BIGINT REGEXP_COUNT(STRING <source>, STRING <pattern>[, BIGINT <start_position>])

    Parameters

    • source: Required. A value of the STRING type. The string to search.

    • pattern: Required. A constant of the STRING type or a regular expression. The substring or regular expression to match. For more information about regular expression specifications, see Regular expressions.

    • start_position: Optional. A constant of the BIGINT type. The value must be greater than 0. If you do not specify this parameter, the default value is 1. This value indicates that the search starts from the first character of the source string.

    Return value

    A value of the BIGINT type is returned. The following rules apply:

    • If no match is found, 0 is returned.

    • If source is not of the STRING type, an error is returned.

    • If pattern is an empty string or is not of the STRING type, an error is returned.

    • If start_position is not of the BIGINT type or its value is less than or equal to 0, an error is returned.

    • If source, pattern, or start_position is NULL, NULL is returned.

    Examples

    • Example 1: Calculate the number of matches for a specified pattern in abababc, starting from a specified position.

      • Calculate the number of substrings that match the regular expression a.c in the string abababc. This regular expression matches substrings that start with a, end with c, and contain any single character in between.

        --Returns 1.
SELECT REGEXP_COUNT('abababc', 'a.c');

      • Calculate the number of substrings that match the regular expression [[:alpha:]]{2} in the string abababc, starting from the third character. This regular expression matches any two consecutive letters.

        --Returns 2.
SELECT REGEXP_COUNT('abababc', '[[:alpha:]]{2}', 3);

    • Example 2: An input parameter is NULL.

      --Returns NULL.
SELECT REGEXP_COUNT('abababc', null);

    • Example 3: Calculate the number of colons (:) in the JSON string {"account_id":123456789,"account_name":"allen","location":"hangzhou","bill":100}.

      --Returns 4.
SELECT REGEXP_COUNT('{"account_id":123456789,"account_name":"allen","location":"hangzhou","bill":100}',':');

    Related functions

    REGEXP_COUNT is a string function. For more information about functions related to string searches and conversion, see String functions.