Search This Blog

Number of words in a string

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

No comments: