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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Script Library
 Counting of occurrences of a word in a text field
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Stoad
Freaky Yak Linguist

*
1983 Posts

Posted - 10/27/2003 :  06:44:40  Show Profile  Visit Stoad's Homepage  Reply with Quote
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.

Edited by - Stoad on 10/28/2003 06:07:09

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 10/27/2003 :  07:43:07  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
couldn't you say:

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

or something like that ?

- Jeff
Go to Top of Page

Stoad
Freaky Yak Linguist

*
1983 Posts

Posted - 10/27/2003 :  08:38:23  Show Profile  Visit Stoad's Homepage  Reply with Quote
Yes!! It looks very smart!
Go to Top of Page

MakeYourDaddyProud
Posting Yak Master

United Kingdom
184 Posts

Posted - 10/27/2003 :  09:38:50  Show Profile  Reply with Quote
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
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 10/27/2003 :  09:50:38  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
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
Go to Top of Page

Stoad
Freaky Yak Linguist

*
1983 Posts

Posted - 10/27/2003 :  13:13:05  Show Profile  Visit Stoad's Homepage  Reply with Quote
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.
Go to Top of Page

MakeYourDaddyProud
Posting Yak Master

United Kingdom
184 Posts

Posted - 10/28/2003 :  04:41:54  Show Profile  Reply with Quote
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
Go to Top of Page

Stoad
Freaky Yak Linguist

*
1983 Posts

Posted - 10/28/2003 :  05:28:34  Show Profile  Visit Stoad's Homepage  Reply with Quote
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).
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000