get word count of the input string

USE
[Company]

GO

/****** Object: UserDefinedFunction [dbo].[WordCount] Script Date: 08/25/2009 11:28:42 ******/

SET
ANSI_NULLS ON

GO

SET
QUOTED_IDENTIFIER ON

GO

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



select
dbo.wordcount ('r e d d e p p a')