Get Word Count of a Column using SQL

I have a table with a column called Description. The column is populated with text data. I want to create a query that returns the amount of words in each description.

My thought was to create a function that takes in a value, and returns the amount of words found in the inputted text.

SELECT dbo.GetWordCount(Description) FROM TABLE

For example, if the description is “Hello World! Have a nice day.”, the query should return 6.

How can I get the word count of the description column?

Answer

See this proposed solution: http://www.sql-server-helper.com/functions/count-words.aspx

CREATE FUNCTION [dbo].[WordCount] ( @InputString 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(@InputString)
BEGIN
    SET @Char     = SUBSTRING(@InputString, @Index, 1)
    SET @PrevChar = CASE WHEN @Index = 1 THEN ' '
                         ELSE SUBSTRING(@InputString, @Index - 1, 1)
                    END

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

    SET @Index = @Index + 1
END

RETURN @WordCount

END
GO

Usage Example:

DECLARE @String VARCHAR(4000)
SET @String = 'Health Insurance is an insurance against expenses incurred through illness of the insured.'

SELECT [dbo].[WordCount] ( @String )