January 9, 2015 06:01 by
Peter
In SQL Server there is not any inherent capacity accessible for discovering the number of words in a String. Here I reveal to both of you diverse methodologies for doing this, the first is the most simpleone, 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 said prior, the above query will provides for you the right result, just if the words are differentiated with a solitary space. Presently on the off chance that they are differentiated by more than one space, this will provide for you off base results as the results are basically relied on upon Length of the original string. Along these lines, what will be the arrangement, simply compose 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 Server2012')
SELECT dbo.udfWordCount ('SQL Server 2012 ')