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  SQL Server Development (2000)  Sql search rated multiple words not using freetext

Author  Topic  iqula Starting Member 1 Post Posted - 2004-04-15 : 04:10:58 Hi I have developed a function to do a rated search without using the free text search it supports multiple words in any order, and would like to share it. It’s a bit of a donkey so any ideas to speed it up would be nice. The rating comes out as a percentage%. CREATE function dbo.fn_SearchRating( @Content nvarchar(4000), @String nvarchar (4000) ) returns int as begin --Test for bad input to speed things up if @String=null or @String='' begin return 999 end --Declare vars declare @NextString nvarchar(4000) declare @Pos int declare @Delimiter nvarchar(1) declare @Score int declare @Count int --Initialize Set @Delimiter=' ' Set @Score=0 Set @Count=0 Set @NextString = '' Set @String = @String + @Delimiter --Get position of first Comma Set @Pos = charindex(@Delimiter,@String) --Loop while there is still a comma in the String of levels while (@pos <> 0) begin Set @Count=@Count+1 Set @NextString = substring(@String,1,@Pos - 1) if @content like '% ' + @NextString+ '%' Set @Score=@Score+1 set @String = substring(@String,@pos +1,len(@String)) Set @Pos = charindex(@Delimiter,@String) end return (@Score *100/@Count) end And here is a demo proc that uses it CREATE PROCEDURE dbo.products_get ( @productid int=null, @categoryid int =null, @searchstr nvarchar(50)=null, ) AS if @productid=0 set @productid=null if @categoryid=0 set @categoryid=null if @searchstr='' set @searchstr=null --logic to deal with what gets returned according to what was input. Select rating=dbo.fn_SearchRating(products.description,@searchstr) , * From products Where productid=isnull(@productid,productid) and categoryid=isnull(@categoryid,categoryid) and dbo.fn_SearchRating(products.description,@searchstr)>0 Order by dbo.fn_SearchRating(products.description,@searchstr) desc, categoryid RETURN @@error GO