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) )
DECLARE @Index INT
DECLARE @Char CHAR(1)
DECLARE @PrevChar CHAR(1)
DECLARE @WordCount INT
SET @Index = 1
SET @WordCount = 0
WHILE @Index <= LEN(@InStr)
SET @Char = SUBSTRING(@InStr, @Index, 1)
SET @PrevChar = CASE WHEN @Index = 1 THEN ' '
ELSE SUBSTRING(@InStr, @Index - 1, 1)
IF @PrevChar = ' ' AND @Char != ' '
SET @WordCount = @WordCount + 1
SET @Index = @Index + 1
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 ,