Count Word In SQL Server

In this blogI am going to write a function which returns the word count in sql server.

Sql server does not provide inbuilt function for calculating word count. So we have to write a custom function in sql.

CREATE FUNCTION [dbo].[fn_WordCount] ( @Param VARCHAR(4000) )
RETURNS INT
AS
BEGIN
DECLARE @Index          INT
DECLARE @Char           CHAR(1)
DECLARE @PrevChar       CHAR(1)
DECLARE @WordCount      INT
SET @Index = 1
SET @WordCount = 0

WHILE @Index <= LEN(@Param)
BEGIN
    SET @Char     = SUBSTRING(@Param, @Index, 1)
    SET @PrevChar = CASE WHEN @Index = 1 THEN ' '
                         ELSE SUBSTRING(@Param, @Index - 1, 1)
                    END

    IF @PrevChar = ' ' AND @Char != ' '
        SET @WordCount = @WordCount + 1

    SET @Index = @Index + 1
END
RETURN @WordCount
END

Let's call this function and see the output. 

select dbo.fn_wordcount('this is test function in sql') as wordcount