SQL Server Portal

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.