REGEXP_COUNT
REGEXP_COUNT is a function in SQL that calculates the number of times a pattern occurs in a string. The pattern, specified in the form of a regular expression, searches the given string and returns the number of matches found. This function is case-sensitive and can be a powerful tool when analyzing textual data.
REGEXP_COUNT(source_string, pattern, position, match_parameter)
- source_string: The string in which the pattern will be searched.
- pattern: The regular expression pattern that is to be matched in the source string.
- position: Determines where in the source string to begin the search. Default value is 1, which signifies that the search will start from the first character of the string.
- match_parameter: An optional parameter which allows different types of pattern matching, including case sensitivity and line matching. If this parameter is not provided, the function uses default matching parameters.
Example
SELECT REGEXP_COUNT('Hello World', 'o') AS count_oFROM dual;Output
COUNT_O----------2Explanation
The REGEXP_COUNT function in Oracle is used to count the number of times a pattern occurs in a string. In the provided example, it counts the number of ‘o’ in the string ‘Hello World’ and returns the count as ‘2’.