REGEXP_COUNT
Returns the number times a regular expression matches a string.
Syntax
REGEXP_COUNT( string, pattern [, position [, regexp_modifier ] ] )
Parameters
string
|
The |
||||||||||||
pattern |
The regular expression to search for within |
||||||||||||
position
|
[Optional] The number of characters from the start of the string where the function should start searching for matches. By default, the function begins searching for a match at the first (leftmost) character. Setting this parameter to a value greater than 1 begins searching for a match at the nth character you specify. Default value: 1 |
||||||||||||
regexp_modifier
|
[Optional] One or more single-character flags that modify how the regular expression finds matches in string:
|
Notes
This function operates on UTF-8 strings using the default locale, even if the locale has been set to something else.
If you are porting a regular expression query from an Oracle database, remember that Oracle considers a zero-length string to be equivalent to NULL, while Vertica does not.
Examples
Count the number of occurrences of the substring an in the string "a man, a plan, a canal, Panama."
=> SELECT REGEXP_COUNT('a man, a plan, a canal: Panama', 'an'); REGEXP_COUNT -------------- 4 (1 row)
Find the number of occurrences of the substring an in the string "a man, a plan, a canal: Panama" starting with the fifth character.
=> SELECT REGEXP_COUNT('a man, a plan, a canal: Panama', 'an',5); REGEXP_COUNT -------------- 3 (1 row)
Find the number of occurrences of a substring containing a lower-case character followed by an. In the first example, do not use a modifier. In the second example, use the i modifier to force the regular expression to ignore case.
=> SELECT REGEXP_COUNT('a man, a plan, a canal: Panama', '[a-z]an'); REGEXP_COUNT -------------- 3 (1 row)
=> SELECT REGEXP_COUNT('a man, a plan, a canal: Panama', '[a-z]an', 1, 'i'); REGEXP_COUNT -------------- 4