Oracle Function to Count Pattern (Word) in String

Oracle Function to Count Pattern(Word) in String

There is no in built function in oracle to count word or pattern is repeated how many times in a string.

Following user defined function could be used to achieve the same.

(Note:- Function returns NULL when bad input paramters are passed):-

CREATE OR REPLACE FUNCTION pattern_count

( p_string VARCHAR2, p_pattern VARCHAR2, p_ignore_case VARCHAR2 DEFAULT 'YES')

RETURN NUMBER

/*=======================================

This function returns number of times, given pattern is present in a given string.

Paramters are as follows:-

p_string varchar2 - String to be checked for pattern

p_pattern varchar2 - Pattern to be checked

p_ignore_case - Whether to do case sensitive search or not.

By default search is not case sensitive.

Function will return NULL if bad input parameters are passed. =======================================*/

IS

v_count NUMBER := 0;

v_string VARCHAR2 (32767) := p_string;

v_pattern VARCHAR2 (32767) := p_pattern;

v_ignore_case VARCHAR2 (3) := INITCAP (p_ignore_case);

BEGIN

IF (v_ignore_case NOT IN ('Yes', 'No')) OR (v_string is null) OR (v_pattern is null)

THEN

RETURN NULL;

END IF;

IF (v_ignore_case IN ('Yes'))

THEN

v_string := UPPER (p_string);

v_pattern := UPPER (p_pattern);

END IF;

WHILE INSTR (v_string, v_pattern) > 0

LOOP

v_count := v_count + 1;

v_string := SUBSTR (v_string, INSTR (v_string, v_pattern) + 1);

END LOOP;
RETURN v_count;

END pattern_count;

/

Following are few query results using this function:-

-- Case Sensitive Searches

architk@TESTDB10g>select pattern_count('ArchitK;ArchitK','it','No') as pattern_count from dual;
PATTERN_COUNT
-------------
2
architk@TESTDB10g>select pattern_count('ArchitK;ArchitK','iT','No') as pattern_count from dual;
PATTERN_COUNT
-------------
0

-- Case Insensitive Searches

architk@TESTDB10g>select pattern_count('ArchitK;ArchitK','iT','Yes') as pattern_count from dual;
PATTERN_COUNT
-------------
2
architk@TESTDB10g>select pattern_count('ArchitK;ArchitK','IT','Yes') as pattern_count from dual;
PATTERN_COUNT
-------------
2

---Bad Parameters
architk@TESTDB10g>select nvl(pattern_count(null,';','No'),-1) as pattern_count from dual;
PATTENR_COUNT
-------------
-1
architk@TESTDB10g>select nvl(pattern_count('ArchitK;Architk',null,'No'),-1) as pattern_count from dual;
PATTENR_COUNT
-------------
-1
architk@TESTDB10g>select nvl(pattern_count('ArchitK;Architk',';','bad'),-1) as pattern_count from dual;
PATTENR_COUNT
-------------
-1