SQL : Count String Occurrence

in SQl server, we dont have any function which can return the number of words in a perticular string .but from small tricks we can do that . now Consider here we can separating the character on space basis

example :


declare @Str VARCHAR(2000)
SET @Str = 'Hi this is Test line which count no of spaces.'
select LEN(@Str) - LEN(Replace(@Str,' ', '')) + 1


Above query will return value 10 , but if the words are separate by more than one space then it will aslo count that space. but its wrong as per the answer.
in that case Create one function which can keep multiple spaces as a single space and return proper result

Below is a Function which can remove white space and all and retrun peoper result.

CREATE FUNCTION [dbo].[WordCount] ( @inStr 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(@InStr)
BEGIN
SET @Char = SUBSTRING(@InStr, @Index, 1)
SET @PrevChar = CASE WHEN @Index = 1 THEN ' '
ELSE SUBSTRING(@InStr, @Index - 1, 1)
END

IF @PrevChar = ' ' AND @Char != ' '
SET @WordCount = @WordCount + 1

SET @Index = @Index + 1
END

RETURN @WordCount
END
GO



now you can use that WordCount Function

DECLARE @String VARCHAR(4000)
SET @String = 'Hi this is Test line which count no of spaces.'
SELECT [dbo].[WordCount] ( @String )


Above Query will return Result 10 not any other,
hope you like this ,