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). |
 |
|
|