Counting of occurrences of a word in a text field

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

General SQL Server Forums

Script Library

Counting of occurrences of a word in a text field

Author

Topic

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-10-27 : 06:44:40

Finding numbers of occurrences of a string of characters
in a column of TEXT datatype.
DDL of involved table txt:
create table txt (pk int, txtcol text) -- datatype of pk doesn't matter

declare @word varchar(80) set @word='help'
declare @pk int, @count int, @i int, @dl int, @wl int
set @wl=len(@word)
declare abc cursor for select pk from txt
where patindex('%'+@word+'%',txtcol)>0 order by pk
open abc fetch next from abc into @pk
while @@fetch_status=0
begin
select @dl=datalength(txtcol) from txt where pk=@pk
select @i=patindex('%'+@word+'%',txtcol)+@wl from txt where pk=@pk
set @count=1
while @i<@dl
begin
select @count=@count+(len(substring(txtcol,@i,8000))-
len(replace(substring(txtcol,@i,8000),@word,'')))/@wl
from txt where pk=@pk
set @i=@i+8001-@wl
end
select pk=@pk, occurrences=@count
fetch next from abc into @pk
end
close abc deallocate abc


pk          occurrences 
----------- ----------- 
1           1

pk          occurrences 
----------- ----------- 
2           2

pk          occurrences 
----------- ----------- 
3           11

Edit: as suggested-reminded by jsmith8858.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-10-27 : 07:43:07

couldn't you say:

(Length(@String) - Length(Replace(@String, @Word, ''))) / Len(@Word)

or something like that ?

- Jeff

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-10-27 : 08:38:23

Yes!! It looks very smart!

MakeYourDaddyProud

184 Posts

Posted - 2003-10-27 : 09:38:50

Alas!

quote:

select @j=charindex(@word,substring(txtcol,@i,8000)) from txt where pk=@pk

Aren't you limiting the size of the text column to a varchar equivalent? I thought the objective was for an unlimited size data column via text????

Daniel Small MIAP
www.danielsmall.com IT Factoring

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-10-27 : 09:50:38

quote:
Originally posted by Stoad

Yes!! It looks very smart!

Definitely not my discovery ... it's an old trick ! I've seen it mentioned in the forums as well here and there.

- Jeff

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-10-27 : 13:13:05

Daniel,
not sure what exactly makes you doubting (yes, we never know with
that text datatype), nevertheless, I bcp-ed text files for testing, each
of them about of 250 kB size. And test gone OK.
The point is (as I understand it): yes, we can cut from text string max
8000 varchars string, but we can cut it starting from any byte within 2 GB.
Seems, the SUBSTRING() and PATINDEX() are the only functions
available for direct handling of text fields.
And, of course, in my code I take into account that searched-for word
may be 'split' by two adjacent varchar(8000) strings.

Jeff,
:) ... anyway I must correct my code and use this trick.

MakeYourDaddyProud

184 Posts

Posted - 2003-10-28 : 04:41:54

My fault, I took it to be the whole string, not the substring section... Too many Scotches making my eyes fizzy...

Daniel Small MIAP
www.danielsmall.com IT Factoring

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-10-28 : 05:28:34

LOL, Danny,

I think that my code writing style can make
any eyes fizzy without any Scotches (suppose
I guessed right what are those Scotches).

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources