Monthly Archives: November 2010

SQL: How to count words

 Here’s a user-defined function that returns the number of words there are in the given input string.

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

What the user-defined function does is to go through each character in the input string.  It increments the word count variable (@WordCount  ) by 1 every time it encounters a non-blank character and the previous character is a space (‘IF @PrevChar = ‘ ‘ AND @Char != ‘ ‘).  So even if there are multiple spaces separating each word, the word will only be counted once.

How to use it:SELECT [dbo].[WordCount] ('Lorem Ipsum is simply dummy text of the printing') (output will be 9)

Source: SQL Server Helper