SQL program to count the number of vowels

[Former Member]

Hi Folks,

Can you please help me with a SQL program to display all column of a employee table where the number of vowels in FIRSTNAME AND LASTNAME is more than 5.

Regards

Nitin

[lbreddemann · Accepted solution]

One way to approach such a problem is what I just did:

1. Google "sql vowel" and find http://stackoverflow.com/questions/13753498/select-vowels-from-a-varchar-oracle-pl-sql
2. Look up REGEX functions in the SAP HANA documentation and find OCCURRENCES_REGEXPR - SAP HANA SQL and System Views Reference - SAP Library
3. Fiddle a bit with it to come up with a query that counts vowels for one column

select user_name,

     OCCURRENCES_REGEXPR ( '[aeiou]' FLAG 'i'  in user_name) as VOWEL_COUNT

from public.users;

USER_NAME	VOWEL_COUNT
SYS	0
SYSTEM	1
_SYS_STATISTICS	3
_SYS_EPM	1
_SYS_REPO	2
_SYS_AFL	1
DEVDUDE	3
ADMIN	2
PUBLICJOE	4
DEDUDE	3
JDBCDUDE	2
OZDUDE	3
_SYS_TASK	1
_SYS_XB	0

From here it's trivial to come to a combined firstname/lastname vowel count.

- Lars

[Former Member · Accepted solution]

Hi Lars

unfortunately i am on SP08 , the function OCCURRENCES_REGEXPR is added on sps9.

I will update once the program is ready in sps9.

Thanks for help

Regards

Nitinr

[lbreddemann · Accepted solution]

Goodness... working with OLD tools... alright.

How about removing all vowels from the string and checking how many characters you lost?

select user_name,

     length( user_name) -

     length (replace (

        replace (

        replace (

        replace (

        replace (upper(user_name)

            , 'A', '')

            , 'E', '')

            , 'I', '')

            , 'U', '')

            , 'O', '')

            ) vowel_count_stoneage,

          OCCURRENCES_REGEXPR ( '[aeiou]' FLAG 'i'  in user_name) as VOWEL_COUNT

from public.users;

Not as fancy, but does it's job.

- Lars

[Former Member · Accepted solution]

Hi Lars,

It is working fine now. Thanks for the help.

Regards

NitinR