T-SQL function to get word count

Similar in approach to the previous uf_charCount function, the following function returns the number of words in a string, assuming a space token as word separator.

CREATE FUNCTION [dbo].[uf_wordCount] (@string varchar(8000))
RETURNS SMALLINT  
/* 
	Purpose: returns the number of words in @string 
	Author: Kevin J. Miller (www.websolete.com)
	
	Example: 
		SELECT dbo.uf_wordCount('four score and seven years ago') 
		would return: 6
		
 */
AS
BEGIN 
	SET @string = LTRIM(RTRIM(ISNULL(@string,''))); 
	IF LEN(@string) = 0 RETURN 0; 
	-- return the difference in length after stripping spaces, this is the word count
	RETURN ((LEN(@string) + 1) - LEN(REPLACE(@string,' ',''))); 
END 
  1. Valy

    #1 by Valy - December 14, 2011 at 10:39 PM

    OK... But if you have @String='Word<space>word2<space><space>word3' the counter gives you 4 words...Not a good function...
(will not be published)
Leave this field empty: