CODEWITHSUNDEEP
sqloracle-databaseplsql
eliasdev
eliasdev

Reputation: 35

Select vowels from a varchar, Oracle PL/SQL

I'm trying to pull up the count of the vowels contained in a varchar, I've been looking around in google, no success though.

Can anyone give me a hand with this one?

Upvotes: 2

Views: 9708

Answers (3)

Ben
Ben

Reputation: 52923

If you're using Oracle 11g, you can use the REXEXP_COUNT function to determine what matches the pattern.

SQL> select regexp_count('andrew', '[aeiou]', 1, 'i') as vowels
  2    from dual;

    VOWELS
----------
         2

The first parameter is the string you want to match, 'andrew'.

The second parameter is the match pattern, in this case [aeiou]. The [] indicates a character list; the parser matches any and all characters inside this list in any order.

The third parameter, 1, is the start position indicating the positional index of the string where Oracle should start searching for a match. It's included solely so I can use the fourth parameter.

The fourth parameter is a match parameter, 'i' indicates that I want to do case insensitive matching. This is the reason why the character list is not [aeiouAEIOU].

If you're using 10g then REGEXP_COUNT doesn't exist. In this case you could use a more exact version of Annjawan's solution with REGEXP_REPLACE.

SQL> select length(regexp_replace('andrew','[^aeiou]', '', 1, 0, 'i')) as vowels
  2    from dual;

    VOWELS
----------
         2

The carat (^) indicates a not, i.e. the replaces every character in the string 'andrew' that is not in the character list [aeiou] with the empty string. The next parameter, once again, is the start position. The fifth parameter, 0 indicates that you want to replace every occurrence of the pattern that matches and once again I've used the match parameter 'i' to indicate case insensitive matching.

Gaurav's answer is incorrect. This is because within the character list he has included comma's. Remember that everything within the character list get's matched if it is available. So, if I introduce a comma into your string you'll have 3 "vowels" in your string:

SQL> select regexp_count('an,drew','[a,e,i,o,u,A,E,I,O,U]' ) as vowels
  2    from dual;

    VOWELS
----------
         3

Regular expressions are not simple beasts and I would highly recommend reading the documentation when attempting them.

Upvotes: 2

Anjan Biswas
Anjan Biswas

Reputation: 7932

Something like

select length(regexp_replace('andrew','[^AEIOUaeiou]')) as vowels from dual;

Upvotes: 3

Gaurav Soni
Gaurav Soni

Reputation: 6346

SELECT length('andrew')
          - length(REGEXP_REPLACE('andrew','[a,e,i,o,u,A,E,I,O,U]','')) 
FROM DUAL;

Output:2 -- a and e are two vowels here.

If you are using Oracle 11g then

SELECT REGEXP_COUNT('andrew','[a,e,i,o,u,A,E,I,O,U]' ) from dual

Upvotes: 1

Related Questions