REGEXP_COUNT

The REGEXP_COUNT function returns a count of the number of times that a regular expression pattern is matched in a string.

source-string: An expression that specifies the string in which the search is to take place. The expression must return a value that is a built-in character string, graphic string, numeric, or datetime data type. If the value is not a UTF-16 DBCLOB, it is implicitly cast to a UTF-16 DBCLOB before searching for the regular expression pattern. A character string with the FOR BIT DATA attribute or a binary string is not supported. The length of a string must not be greater than 1 gigabyte.
pattern-expression: An expression that specifies the regular expression string that is the pattern for the search. The expression must return a value that is a built-in character string, graphic string, numeric, or datetime data type. If the value is not a UTF-16 DBCLOB, it is implicitly cast to a UTF-16 DBCLOB before searching for the regular expression pattern. A character string with the FOR BIT DATA attribute or a binary string is not supported. The length of the string must not be greater than 32K.
A valid pattern-expression consists of a set of characters and control characters that describe the pattern of the search. For a description of the valid control characters, see Regular expression control characters.

start: An expression that specifies the position within source-string at which the search is to start. The expression must return a value of any built-in numeric, character-string, or graphic-string data type. The argument is cast to INTEGER before evaluating the function. For more information about converting to INTEGER, see INTEGER or INT. The value of the integer must be greater than or equal to 1. If the value of the integer is greater than the actual length of the source-string, the result is 0.

flags: An expression that specifies flags that control aspects of the pattern matching. The expression must return a value that is a built-in character string or graphic string data type. A character string with the FOR BIT DATA attribute or a binary string is not supported. The string can include one or more valid flag values and the combination of flag values must be valid. An empty string is the same as the value 'c'.
For a description of the valid flag characters, see Regular expression flag values.

The result of the function is an INTEGER representing the number of occurrences of the pattern-expression within the source-string. If the pattern-expression is not found and no argument is null, the result is 0.

If any argument of the REGEXP_COUNT function can be null, the result can be null. If any argument is null, the result is the null value.

Notes

Prerequisites: In order to use the REGEXP_COUNT function, the International Components for Unicode (ICU) option must be installed

Processing: The regular expression processing is done using the International Components for Unicode (ICU) regular expression interface. For more information see, http://userguide.icu-project.org/strings/regexp.

If only three arguments are specified, the third argument may be a start or flags argument. If the third argument is a string, it is interpreted as a flags argument. Otherwise, it is interpreted as a start argument.

Syntax Alternatives: REGEXP_MATCH_COUNT is a synonym for REGEXP_COUNT.

Example

Count the number of times "Steven" or "Stephen" occurs in the string "Steven Jones and Stephen Smith are the best players".
```
SELECT REGEXP_COUNT( 
  'Steven Jones and Stephen Smith are the best players',
  'Ste(v|ph)en')
FROM sysibm.sysdummy1
```
The result is 2.