SQL Server Performance Forum – Threads Archive

Word count

Dear All,
I need to find out the words count in a single column.
Ex : col1
‘Thank You Very Much’ I need to display the count of the words in that column. can u pls help me out? Thanks in Advance Jai

A = the length of the string using LEN().
B = the length of the string after using REPLACE() to remove any spaces.
Then A – B is the number of spaces in the string.
And finally (A – B) + 1 is the number of words. SELECT
(LEN(column) – LEN(REPLACE(column, ‘ ‘, ”))) + 1
FROM table

Thank you Adriaan

Hi, This artile will give u a better idea http://www.sql-server-helper.com/functions/count-words.aspx Madhu

<blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by madhuottapalam Hi, This artile will give u a better idea http://www.sql-server-helper.com/functions/count-words.aspx</a> <hr height="1" noshade id="quote"></blockquote id="quote"> The UDF from your link isn’t terribly thrilling, if you ask me. [<img src=’/community/emoticons/emotion-5.gif’ alt=’

‘ />] I don’t see a reason for a UDF here anyway and especially not for a UDF that walks through the input string character by character. Adriaan’s solution is pretty good. Probably even better would be to do this at the front end. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] – Frank Kalis Moderator Microsoft SQL Server MVP Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>

Subscribing here ——————
Bug explorer/finder/seeker/locator
——————

Doing at the front end is better or you should CLR functions for this type of fuctionality.
Mohammed U.

Speaking of the function, there is an interesting point mentioned which relates to Adriaan’s solution. Look at this example with space: It should return 4 words instead of returning 11, right? CREATE TABLE #test
(SomeValue varchar(50)
)
INSERT INTO #test(SomeValue )
VALUES (‘Thank You Very Much’) SELECT
(LEN(SomeValue ) – LEN(REPLACE(SomeValue , ‘ ‘, ”))) + 1 AS [Number of Words]
FROM #test drop table #test Number of Words
—————
11 (1 row(s) affected)
Thanks,
DilliGrg

Interesting. A good reason to do this at the client. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] – Frank Kalis Moderator Microsoft SQL Server MVP Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>

DECLARE @string
SET @string = ‘xxx x xxxxxxxxxxx’ WHILE @string LIKE ‘% %’
BEGIN
SET @string = REPLACE(@string, ‘ ‘, ‘ ‘)
END … and finish like before. It never stops – you can always think of exceptions.

<blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by FrankKalis Interesting. A good reason to do this at the client. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] – Frank Kalis Moderator Microsoft SQL Server MVP Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a> <hr height="1" noshade id="quote"></blockquote id="quote">This is what I prefer to say [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] Madhivanan Failing to plan is Planning to fail

I know, but you were very late on this one, so I jumped in. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] – Frank Kalis Moderator Microsoft SQL Server MVP Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>

<blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by FrankKalis I know, but you were very late on this one, so I jumped in. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] – Frank Kalis Moderator Microsoft SQL Server MVP Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a> <hr height="1" noshade id="quote"></blockquote id="quote"> [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] Thanks, DilliGrg

Hey, isn’t it a bit late (or early, for that matter) in the place where you live? [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] – Frank Kalis Moderator Microsoft SQL Server MVP Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>

<blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by FrankKalis Hey, isn’t it a bit late (or early, for that matter) in the place where you live? [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] – Frank Kalis Moderator Microsoft SQL Server MVP Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a> <hr height="1" noshade id="quote"></blockquote id="quote"> Frank, It’s almost 1am. Going late in the morning for work. [<img src=’/community/emoticons/emotion-5.gif’ alt=’

‘ />] Thanks, DilliGrg

<blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by FrankKalis Hey, isn’t it a bit late (or early, for that matter) in the place where you live? [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] – Frank Kalis Moderator Microsoft SQL Server MVP Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a> <hr height="1" noshade id="quote"></blockquote id="quote"> Did you ask this question to me? Madhivanan Failing to plan is Planning to fail

No. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] – Frank Kalis Moderator Microsoft SQL Server MVP Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>

]]>