Word Counting Function in SQL Server

Hello to everyone,

In this article, I will talk about the use of word counting functions in SQL Server.

In SQL Server you may want to find the number of words in a given sentence in some cases.

You can easily do this using the function below.

CREATE FUNCTION [dbo].[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;

--Using the Function

SELECT dbo.WordCount('MSSQL Query Best SQL Server Sites') AS WordCount;

When you create the function and run the code, you will get a result as follows.

As you can see, there are 6 words.

Good luck to everyone in business and life.