Sql server does not provide inbuilt function for calculating word count. So we have to write a custom function in sql.
  1. CREATE FUNCTION [dbo].[fn_WordCount] ( @Param VARCHAR(4000) )
  2. RETURNS INT
  3. AS
  4. BEGIN
  5. DECLARE @Index INT
  6. DECLARE @Char CHAR(1)
  7. DECLARE @PrevChar CHAR(1)
  8. DECLARE @WordCount INT
  9. SET @Index = 1
  10. SET @WordCount = 0
  12. WHILE @Index <= LEN(@Param)
  13. BEGIN
  14. SET @Char = SUBSTRING(@Param, @Index, 1)
  15. SET @PrevChar = CASE WHEN @Index = 1 THEN ' '
  16. ELSE SUBSTRING(@Param, @Index - 1, 1)
  17. END
  19. IF @PrevChar = ' ' AND @Char != ' '
  20. SET @WordCount = @WordCount + 1
  22. SET @Index = @Index + 1
  23. END
  24. RETURN @WordCount
  25. END
Let's call this function and see the output.
  1. select dbo.fn_wordcount('this is test function in sql') as wordcount