SQL server count words

Sometime we need to know numbers of words in  sentence but in the SQL server ther is no function  that will returns the numbers of words

In this blog post I will share white you a simple SQL function

 CREATE FUNCTION WordCount ( @Str VARCHAR(4000) )   
 RETURNS INT  
 AS  
 BEGIN  
 DECLARE @Index INT  
 DECLARE @Char CHAR(1)  
 DECLARE @PChar CHAR(1)  
 DECLARE @Nbr INT  
 SET @Index = 1  
 SET @Nbr = 0  
 WHILE @Index <= LEN(@Str)  
 BEGIN  
   SET @Char   = SUBSTRING(@Str, @Index, 1)  
   SET @PChar = CASE WHEN @Index = 1 THEN ' '  
              ELSE SUBSTRING(@Str, @Index - 1, 1)  
           END  
   IF @PChar = ' ' AND @Char != ' '  
     SET @Nbr = @Nbr + 1  
   SET @Index = @Index + 1  
 END  
 RETURN @Nbr  
 END  
 GO