Today we will see a simple User Defined Function to Get Count of the words from a String in MS SQL Server. I know you all must have done this before but its gonna be a pretty interesting because we are doing it from SQL. So lets start now…

CREATE FUNCTION [dbo].[fn_GetWordCount]
(
@InputString VARCHAR(5000)
)
RETURNS INT
AS
BEGIN

DECLARE @Indx INT
DECLARE @Chr CHAR(1)
DECLARE @PreviousChar CHAR(1)
DECLARE @CountWord INT

SET @Indx = 1
SET @CountWord = 0

WHILE @Indx <= LEN(@InputString)
BEGIN
SET @Chr = SUBSTRING(@InputString, @Indx, 1)
SET @PreviousChar = CASE WHEN @Indx = 1 THEN ‘ ‘
ELSE SUBSTRING(@InputString, @Indx – 1,
1)
END

IF @PreviousChar = ‘ ‘
AND @Chr != ‘ ‘
SET @CountWord = @CountWord + 1

SET @Indx = @Indx + 1
END

RETURN @CountWord

END

Note : It will return the word count from any string.