--function that returns number of spaces in string

create function GetSpaceCount(@SearchString nvarchar(2000))

returns int

as

begin

declare @SpacePos int,@SpaceCount int;

set @SpacePos=0;

set @SpaceCount=0;

set @SpacePos=CHARINDEX(' ',@SearchString,@SpacePos)

while @SpacePos<>0

begin

set @SpacePos=CHARINDEX(' ',@SearchString,@SpacePos+1)

set @SpaceCount=@SpaceCount+1

end

return @SpaceCount;

end

--test

select GetSpaceCount('99202 10060 99000 A6402')