Sourced from := oracle-sql-l@Groups.ITtoolbox.com

A question was posed on the listserver regarding the 'how' of identifying how many vowels are in a particular string of text. Not certain as to the purpose of same, however intresting nonetheless. For those on 10g+, use of the REGEXP% syntax comes into play.

DECLARE
v_str varchar2(200):='RAJEEV HERE from lucknow' ;
vn_vowel number;
BEGIN
SELECT length(v_str)- length(REGEXP_REPLACE(v_str,'[a,e,i,o,u,A,E,I,O,U]',''))
into vn_vowel FROM DUAL;
dbms_output.put_line(vn_vowel);
END;

For those on versions prior to 10g, this method was proposed that provided the same result.

select length(COLUMN_NAME) - length( translate(lower(COLUMN_NAME),'zaeiou','z')) from TABLE_NAME ;