Number Of Word In a String

In sql server there is not any built-in function available for finding the No. of words in a String. Here I show you two different approaches for doing this, the first one is the easiest one, and is applicable only of these words are separated by a single space.

DECLARE @String VARCHAR(4000)
SELECT @String = 'SQL Server 2005'
SELECT LEN(@String) - LEN(REPLACE(@String, ' ', '')) + 1

As I mentioned earlier, the above query will gives you the correct result, only if the words are separated with a single space. Now if they are separated by more than one space, this will give you incorrect results as the results are mainly depended on  Length of the original string.  So, what will be the solution, just write a function to do this

CREATE FUNCTION dbo.udfWordCount(
@OriginalText VARCHAR(8000)
)
RETURNS int
as
/*
SELECT dbo.udfWordCount ('hello   world')
*/
BEGIN
     
    DECLARE @i int ,@j INT, @Words int
    SELECT     @i = 1, @Words = 0 

    WHILE @i <= DATALENGTH(@OriginalText)
    BEGIN
        
        SELECT    @j = CHARINDEX(' ', @OriginalText, @i)
        if @j = 0
        BEGIN
            SELECT    @j = DATALENGTH(@OriginalText) + 1
        END
        IF SUBSTRING(@OriginalText, @i, @j - @i) <>' '
              SELECT @Words = @Words +1 
        SELECT    @i = @j +1
    END
    RETURN(@Words)
END

GO
SELECT dbo.udfWordCount ('SQL Server2005')
SELECT dbo.udfWordCount ('SQL Server 2005  ')