help expanding this 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 2000 Forums

Transact-SQL (2000)

help expanding this function?

Author

Topic

dmeikle
Starting Member

1 Post

Posted - 2005-06-09 : 05:20:15

I'm trying to create a method that retrieves the number of occurances a word (or phrase, or group of words) will occur in a text field.

it receives 3 parameters:
the text to examine
the word(s) to search for
the type of search: and/or/exa (exa = exact phrase)

it works fine so far. it even iterates on queries using multiple words. the problem is that the column I work with is a text field, and the contents are often more than 8000 chars in length, so it's a text datatype. I use len and replace functions in the UDF I wrote, and I know I can use DATALENGTH instead of len, but I'm still stuck with the replace function not accepting a text datatype (needs a varchar).

I tried to iterate through the text value and break it into 8000 char varchar datatype, looping until complete, but now I can't pass a text datatype into a UDF. I'm assuming I need to do the iteration on the calling procedure instead...

any suggestions?

here's the function as I wrote it so far:

CREATE FUNCTION fn_GET_NUM_OCCURENCES ( @table varchar(8000), @query varchar(50), @type varchar(3))

RETURNS int
AS
BEGIN

declare @total int
set @total=0

if @type='exa'
set @total= ((len(@table)-len(replace(@table,@query,'')))/len(@query))
else
begin
declare @pos1 int
declare @pos2 int
declare @word varchar(20)
declare @finalLap bit

set @finalLap=0
set @pos1=0
set @pos2=0
set @query=' '+@query
while (charindex(' ',@query,@pos1)>0 and @finalLap=0)
begin

set @pos1=charindex(' ',@query,@pos1-1)
set @pos2=charindex(' ',@query,@pos1+1)-1

if @pos2<1
begin
set @pos2=len(@query)
set @finalLap=1
end
set @word=substring(@query,@pos1,1+@pos2-@pos1)

set @total=@total+ ((len(@table)-len(replace(@table,@word,'')))/len(@word))
set @pos1=@pos2-2
if @pos2<0
set @finalLap=1

end

end
return @total
END

nr
SQLTeam MVY

12543 Posts

Posted - 2005-06-09 : 06:32:52

see
http://www.mindsdoor.net/SQLTsql/ReplaceText2.html
for using replace on a text datatype.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources