Feeds:
Posts
Comments

Posts Tagged ‘SQL Server – Enhance word Count Function’

I need to count the words from one of the fields in the table and its data type is varchar (max). This is a very simple task, but the problem is, varchar field has unnecessary White Spaces, Tabs, Carriage Returns and Line Feeds as well. I searched it over the internet and found a solution but it works when it is a simple sentence without any hurdles or otherwise with loop. So, I thought of developing this solution without loop.

First of all, I found which character control has what char value. Given below are the details :

  • Tab                             char(9)
  • Line feed                    char(10)
  • Carriage return          char(13)
  • White space               char(32)

Solution : (Word count for complex paragraphs having unnecessary White Spaces, Tabs, Carriage Returns and Line Feeds)

CREATE FUNCTION dbo.[UDF_Word_Count_From_String]
(
 @STRING VARCHAR(MAX) -- Variable for string
)
RETURNS VARCHAR(MAX)
BEGIN

    DECLARE @RETURNSTRING AS VARCHAR(MAX)

	Set @STRING=REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@STRING
	,CHAR(9),CHAR(32))
	,CHAR(13),CHAR(32))
	,CHAR(10),CHAR(32))
	,CHAR(32),'øù')
	,'ùø','')
	,'øù',CHAR(32))
	SELECT @RETURNSTRING =LEN(@String) - LEN(REPLACE(@String,' ', '')) + 1
RETURN @RETURNSTRING
END
GO
Select dbo.[UDF_Word_Count_From_String]('How many
word		do   you
have in this sentence?') as [Word Count]
--OUTPUT

Word Count
——————–
9

(1 row(s) affected)

Let me know if you know a better solution.

Read Full Post »