In this blog i am going to write a function which returns word count in sql server.
Sql server does not provide inbuilt function for calculating word count. So we have to write a custom function in sql.
CREATE FUNCTION [dbo].[fn_WordCount] ( @Param 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(@Param) BEGIN SET @Char = SUBSTRING(@Param, @Index, 1) SET @PrevChar = CASE WHEN @Index = 1 THEN ' ' ELSE SUBSTRING(@Param, @Index - 1, 1) END IF @PrevChar = ' ' AND @Char != ' ' SET @WordCount = @WordCount + 1 SET @Index = @Index + 1 END RETURN @WordCount END
Lets call this function and see the output.
select dbo.fn_wordcount('this is test function in sql') as wordcount
I hope this blog useful to you.
Happy coding 🙂