need help with function - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Analysis Server and Reporting Services (2005)

need help with function

Author

Topic

Tart_SQL
Starting Member

41 Posts

Posted - 2008-02-27 : 12:40:05

Hi,

Thank you for looking at this. I am trying to write a function that would count the number of words in a column. The count of null columns should be zero, but the problem am having is some of the columns may have more than one spaces between words. I am still trying to figure out why my code doesn't work. Any help will be appreciated.

Here is my function. (It does not matter what the nature of words are, hence am not concerned about regular expressions I just want the count).

alter function displayNumberofWords(@colValue varchar(max))
returns int
as
begin
declare @counter int, @currentColValue varchar(max), @spaceIndex int

set @currentColValue = @colValue

if (@currentColValue) = null
begin
set @counter = 0
end
else
begin
set @counter = 1
end

while (len(@currentColValue)) >= 1
begin
--set @counter = 1

set @spaceIndex = charindex(' ', @currentColValue)

if @spaceIndex = 0
begin
set @currentColValue = ''
end

if @spaceIndex > 1
begin
set @currentColValue = substring(@currentColValue, @spaceIndex + 1,
len(@currentColValue) - @spaceIndex)
set @counter = @counter + 1
end
else
begin
set @currentColValue = substring(@currentColValue, @spaceIndex + 1,
len(@currentColValue) - @spaceIndex)
end
end

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2008-02-27 : 14:47:05

Search for count words here at SQLTeam

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-02-28 : 02:18:56

Refer this
http://sqlblogcasts.com/blogs/madhivanan/archive/2007/12/21/count-number-of-words-in-a-string.aspx

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources