In sql server there is not any built-in function available for finding the No. of words in a String. Here I show you two different approaches for doing this, the first one is the easiest one, and is applicable only of these words are separated by a single space.
DECLARE @String VARCHAR(4000)
SELECT @String = 'SQL Server 2005'
SELECT LEN(@String) - LEN(REPLACE(@String, ' ', '')) + 1
As I mentioned earlier, the above query will gives you the correct result, only if the words are separated with a single space. Now if they are separated by more than one space, this will give you incorrect results as the results are mainly depended on Length of the original string. So, what will be the solution, just write a function to do this
CREATE FUNCTION dbo.udfWordCount(
@OriginalText VARCHAR(8000)
)
RETURNS int
as
/*
SELECT dbo.udfWordCount ('hello world')
*/
BEGIN
DECLARE @i int ,@j INT, @Words int
SELECT @i = 1, @Words = 0
WHILE @i <= DATALENGTH(@OriginalText)
BEGIN
SELECT @j = CHARINDEX(' ', @OriginalText, @i)
if @j = 0
BEGIN
SELECT @j = DATALENGTH(@OriginalText) + 1
END
IF SUBSTRING(@OriginalText, @i, @j - @i) <>' '
SELECT @Words = @Words +1
SELECT @i = @j +1
END
RETURN(@Words)
END
GO
SELECT dbo.udfWordCount ('SQL Server2005')
SELECT dbo.udfWordCount ('SQL Server 2005 ')
