To get word count of the input String

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')