How to count number of vowel - SQL Server

Question

I want to write a procedure like count number of vowel in a column. So if I need it any time, then I do not need to write the procedure again. I want to use like find_vowel();

Example:

Select Column_String 
From Tablo1 
Where Column_ID=1

Result: "I Am gonna find it"

Vowel: "I,A,o,a,i,i"

There are 6 vowels in the column (including upper - lower characters).

So how can I find the number of vowels in the columns?

I'm using Microsoft SQL Server 2014

Thanks

Answer 1

Replace all vowels with blank (to delete them) then subtract the length of the vowel-less string from the original length:

select
    len(Column_String)
    - len(
        replace(replace(replace(replace(replace(
            lower(Column_String), 'a', ''), 'e', ''), 'i', ''), 'o', ''), 'u', '')
    ) as vowel_count
from ...

---

As a function:

create function vowel_count(str nvarchar(1024))
returns int
as begin
    return (
      len(str) -
      len(replace(replace(replace(replace(replace(
            lower(str), 'a', ''), 'e', ''), 'i', ''), 'o', ''), 'u', ''));
end;