word count - 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

Transact-SQL (2005)

word count

Author

Topic

RoniR
Starting Member

16 Posts

Posted - 2009-10-01 : 08:09:28

hey guys i have this function

GO
/****** Object: UserDefinedFunction [dbo].[ScrapeText] Script Date: 10/01/2009 13:39:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER FUNCTION [dbo].[ScrapeText]
(
@string varchar(8000)
)
RETURNS varchar(8000)

AS
BEGIN
DECLARE @text varchar(8000),
@PenDown char(1),
@char char(1),
@len int,
@count int
SELECT @count = 0,
@len = 0,
@text = ''

-- BEGIN CALLOUT A
-- BEGIN COMMENT
-- Wrap the input string with tags.
-- END COMMENT
SELECT @string = '>' + @string + '<'

-- BEGIN COMMENT
-- Replace special characters.
-- END COMMENT
SELECT @string = REPLACE(@string,' ',' ')

-- BEGIN COMMENT
-- Parse out the formatting codes.
-- END COMMENT
SELECT @len = LEN(@string)
WHILE (@count <= @len)
BEGIN
SELECT @char = SUBSTRING(@string,@count,1)

IF (@char = '>')
SELECT @PenDown = 'Y'
ELSE
IF (@char = '<')
SELECT @PENDOWN = 'N'
ELSE
IF (@PenDown = 'Y')
SELECT @text = @text + @char
SELECT @count = @count + 1
END
RETURN @text
END

it doesnt return a correct word count like the one i get in microsoft word
my text has html tags in in
i dont know
if any1 can help i will appreciate that
thanks

sanoj_av
Posting Yak Master

118 Posts

Posted - 2009-10-01 : 08:45:04

Hi Rony,
I failed to understand what you are trying to do in the function.Firsty your function returns a varchar column but you want to return the wordcount. I think it may be by mistake(changed for testing..). Secondly, what do you try to do by
SELECT @string = REPLACE(@string,' ',' ') ?. And finally what is your string delimieter?

If you simply wants to return the word count you can use an SQL stament like

declare @word varchar(8000)
Select @word='my first Word counting example'
Select len(Ltrim(@word))-len(replace(ltrim(@word),' ',''))+1

It will return the proper word count unless the string contains multiple whitespaces. if it is the case u will have to use a function for removeing the multiple whitespace to a single one. I found the following function in some Other forum and found to be useful.

CREATE function cleanString2(@string varchar(50))
returns varchar(50)
AS
Begin
if charindex(' ', @string) = 0 return @string
set @string = replace(@string, ' ',' ')
while charindex(' ', @string) > 0
select @string = dbo.cleanString2(@string) --recursive call

return rtrim(ltrim(@string))
End

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-10-01 : 08:47:03

If you're just trying to count words

DECLARE @string varchar(50)
DECLARE @count int

SET @count = 0
SET @string = 'asd ,akj kkljsi iek'

WHILE charindex(' ',@string) > 0
BEGIN
SET @count = @count + 1
SELECT @string,@count
SET @string = SUBSTRING(@string,charindex(' ',@string)+1,50)

END

SELECT @count

Jim

Everyday I learn something that somebody else already knew

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-10-01 : 08:56:13

quote:
Originally posted by jimf

If you're just trying to count words

DECLARE @string varchar(50)
DECLARE @count int

SET @count = 0
SET @string = 'asd ,akj kkljsi iek'

WHILE charindex(' ',@string) > 0
BEGIN
SET @count = @count + 1
SELECT @string,@count
SET @string = SUBSTRING(@string,charindex(' ',@string)+1,50)

END

SELECT @count+1

Jim

Everyday I learn something that somebody else already knew

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-10-01 : 09:37:27

Isn't this faster?

DECLARE @string varchar(50)
SET @string = 'asd ,akj kkljsi iek'
SELECT	1 + LEN(@string) - LEN(REPLACE(@string, ' ', ''))

N 56°04'39.26"
E 12°55'05.63"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-10-01 : 09:41:59

quote:
Originally posted by Peso

Isn't this faster?
DECLARE @string varchar(50)
SET @string = 'asd ,akj kkljsi iek'
SELECT 1 + LEN(@string) - LEN(REPLACE(@string, ' ', ''))

N 56°04'39.26"
E 12°55'05.63"

Yes it is. But I just corrected his code to have correct count

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources