Script to count specific string in whole statement

1- Create function that will count specific string in whole statement

CREATE OR REPLACE FUNCTION scott.CountString

( pInput VARCHAR2, pSearchString VARCHAR2 )

RETURN number

as

BEGIN

   RETURN (LENGTH (pInput) - LENGTH (REPLACE (pInput, pSearchString, '')))

          / LENGTH (pSearchString);

END;

/

2- Test it

select scott.CountString ('oracle world','o') from dual;