Yesterday some one ask me, how can i count a vowels from a string in MS SQL 2008.Here is the following SQL function to return count of vowels from a given input string.
User defined function
CREATE FUNCTION COUNT_VOWEL ( @STRING NVARCHAR(MAX) )
RETURNS INT
AS
BEGIN
DECLARE @I INT
DECLARE @COUNT INT
SET @COUNT = 0
SET @I = 1
DECLARE @ANS NVARCHAR(MAX)
DECLARE @VOWEL NVARCHAR(6)
SET @VOWEL = 'AEIOU'
WHILE @I <= LEN(@STRING)
BEGIN
SET @ANS = SUBSTRING(@STRING, @I, 1)
IF @ANS = 'A'
OR @ANS = 'I'
OR @ANS = 'O'
OR @ANS = 'U'
BEGIN
SET @COUNT = @COUNT + 1
END
SET @I = @I + 1
END
RETURN @COUNT
END
Queries
SELECT DBO.COUNT_VOWEL('AAMIR HASAN') AS [VOWELS(S)]
SELECT DBO.COUNT_VOWEL('MAHWISH HASAN') AS [VOWELS(S)]
SELECT DBO.COUNT_VOWEL('SABA KHAN') AS [VOWELS(S)]
SELECT DBO.COUNT_VOWEL('BILL GATE') AS [VOWELS(S)]
SELECT DBO.COUNT_VOWEL('CYNDY KRYSTYN') AS [VOWELS(S)]
Output
Download
You can download source code from Here.