Word count function : Utility Function « Function Procedure Packages « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. Function Procedure Packages
3. Utility Function

SQL> CREATE OR REPLACE FUNCTION wordcount (str IN VARCHAR2)
  2     RETURN PLS_INTEGER
  3  AS
  4     words PLS_INTEGER := 0;
  5     len PLS_INTEGER := NVL(LENGTH(str),0);
  6     inside_a_word BOOLEAN;
  7  BEGIN
  8     FOR i IN 1..len + 1
  9     LOOP
 10        IF ASCII(SUBSTR(str, i, 1)) < 33 OR i > len
 11        THEN
 12           IF inside_a_word
 13           THEN
 14              words := words + 1;
 15              inside_a_word := FALSE;
 16           END IF;
 17        ELSE
 18           inside_a_word := TRUE;
 19        END IF;
 20     END LOOP;
 21     RETURN words;
 22  END;
 23  /

Function created.

SQL>
SQL>
SQL>