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
(1 row(s) affected)
Let me know if you know a better solution.