find the number of words starts with vowels in the string

‘Data base is used to store large amount of da开发者_运维知识库ta’

I need to find the number of words starts with vowels in the above string. Can any one give me idea to do this

---

One way is to use the commonly mentioned "split" user defined function and use it like this:

SELECT COUNT(*)
FROM dbo.fnSplit(‘Data base is used to store large amount of data’, ' ') x
WHERE x.Value LIKE '[aeiouAEIOU]%'

Some examples of the split UDF can be found here: T-SQL: Opposite to string concatenation - how to split string into multiple records

If this is a common query you are running, I think I'd be tempted to consider working out that count at the time the value is inserted into the table and storing the number in the row.

---

declare @str2 varchar(max)='Data base is used to store large amount of data'

Declare @data varchar(max)
Declare @Cnt int
Set @Cnt = 0

While (Charindex(' ',@str2)>0)
Begin
Select @Data = ltrim(rtrim(Substring(@str2,1,Charindex(' ',@str2)-1)))

Set @str2 = Substring(@str2,Charindex(' ',@str2)+1,len(@str2))
if(SUBSTRING(@Data,1,1)='a' or SUBSTRING(@Data,1,1)='e' or SUBSTRING(@Data,1,1)='i' or  SUBSTRING(@Data,1,1)='o' or SUBSTRING(@Data,1,1)='u')
 begin
Set @Cnt = @Cnt + 1
End
End

Select @Data = ltrim(rtrim(@str2))
if(SUBSTRING(@Data,1,1)='a' or SUBSTRING(@Data,1,1)='e' or SUBSTRING(@Data,1,1)='i' or    SUBSTRING(@Data,1,1)='o' or SUBSTRING(@Data,1,1)='u')
 begin
 Set @Cnt = @Cnt + 1
 End

select @Cnt

---

declare @S varchar(100) = 'Data base is used to store large amount of data'

;with cte as
(
  select case when upper(left(@S, 1)) in ('A','E','I','O','U') then 1 else 0 end as vow,
         stuff(@S, 1, charindex(' ', @S+' '), '')+' ' as rest
  where len(@S) > 0
  union all
  select case when upper(left(rest, 1)) in ('A','E','I','O','U') then 1 else 0 end as vow,
         stuff(rest, 1, charindex(' ', rest), '') as rest
  from cte         
  where len(rest) > 0
)
select coalesce(sum(vow), 0)
from cte

---

The OP mentioned doing it without a function, so try this for a single string (provided you have a "Numbers" table):

--sp GEN_FN_GEN_ListToTable
DECLARE @String  varchar(500)
       ,@SplitOn char(1)
SELECT @String='the quick red fox jumped over the lazy brown dog, again.'
      ,@SplitOn=' '


SELECT
    COUNT(*) AS VowelCount
    FROM (SELECT
              LTRIM(RTRIM(SUBSTRING(List2, number+1, CHARINDEX(@SplitOn, List2, number+1)-number - 1))) AS ListValue
              FROM (
                       SELECT @SplitOn + @String + @SplitOn AS List2
                   ) AS dt
                  INNER JOIN Numbers n ON n.Number < LEN(dt.List2)
              WHERE SUBSTRING(List2, number, 1) = @SplitOn
         ) dt2
    WHERE ListValue IS NOT NULL AND ListValue!='' AND LEFT(ListValue,1) IN ('a','e','i','o','u')

OUTPUT:

VowelCount
-----------
2

(1 row(s) affected)

here is an example of how to count the vowels (function free) with many strings in a table(provided you have a "Numbers" table):

DECLARE @YourTable table (RowID int, RowValue  varchar(500))

INSERT INTO @YourTable VALUES (1,'the quick red fox jumped over the lazy brown dog, again.')
INSERT INTO @YourTable VALUES (2,'just another example of some words.')
INSERT INTO @YourTable VALUES (3,'a b c d e f g h i j k l m n o p q r s t u v w x y z')

SELECT
    RowID,COUNT(*) AS VowelCount
    FROM (SELECT
              RowID, LTRIM(RTRIM(SUBSTRING(List2, number+1, CHARINDEX(' ', List2, number+1)-number - 1))) AS ListValue
              FROM (SELECT
                       RowID, ' ' + RowValue + ' ' AS List2
                       FROM @YourTable
                   ) AS dt
                  INNER JOIN Numbers n ON n.Number < LEN(dt.List2)
              WHERE SUBSTRING(List2, number, 1) = ' '
         ) dt2
    WHERE ListValue IS NOT NULL AND ListValue!='' AND LEFT(ListValue,1) IN ('a','e','i','o','u')
    GROUP BY RowID

OUTPUT:

RowID       VowelCount    
----------- -----------
1           2
2           3
3           5

(3 row(s) affected)

You basically need to get a split function and then split on a single space, here is a similar answer which explains about a split function and the usage of it. I'm not sure why the OP doesn't want to use a function though.

---

SELECT * FROM tbl_name WHERE col_name REGEXP 'vowels'