Tip of the Week
Tip for Week of December 13, 2004

Identify and Count Characters

This tip comes from Aui de la Vega, DBA, in Makati, Philippines.

This function provides the number of times a pattern occurs in a string (VARCHAR2).

SQL> CREATE FUNCTION NUM_CHARS(INSTRING VARCHAR2, INPATTERN VARCHAR2)
RETURN NUMBER
IS
COUNTER NUMBER;
NEXT_INDEX NUMBER;
STRING VARCHAR2(2000);
PATTERN VARCHAR2(2000);
BEGIN
COUNTER := 0;
NEXT_INDEX := 1;
STRING := LOWER(INSTRING);
PATTERN := LOWER(INPATTERN);
FOR I IN 1 .. LENGTH(STRING) LOOP
IF (LENGTH(PATTERN) <= LENGTH(STRING)-NEXT_INDEX+1)
AND (SUBSTR(STRING,NEXT_INDEX,LENGTH(PATTERN)) = PATTERN) THEN
COUNTER := COUNTER+1;
END IF;
NEXT_INDEX := NEXT_INDEX+1;
END LOOP;
RETURN COUNTER;
END;

Function created.

*Note: Use CREATE to make sure you are not overwriting an existing function.

Try it out:

SQL> select num_chars('miSSissipPi','i') from dual
2 /

NUM_CHARS('MISSISSIPPI','I')
----------------------------
4

SQL> c/'i'/'ssi'
1* select num_chars('miSSissipPi','ssi') from dual
SQL> /

NUM_CHARS('MISSISSIPPI','SSI')
------------------------------
2

SQL> c/ssi'/ppi'
1* select num_chars('miSSissipPi','ppi') from dual
SQL> /

NUM_CHARS('MISSISSIPPI','PPI')
------------------------------
1
SQL> c/'ppi/'p
1* select num_chars('miSSissippi','p') from dual
SQL> /

NUM_CHARS('MISSISSIPPI','P')
----------------------------
2

SQL> c/p'/s'
1* select num_chars('miSSissippi','s') from dual
SQL> /

NUM_CHARS('MISSISSIPPI','S')
----------------------------
4