question

pFab avatar image
pFab asked

Creating ranked results from array parameter

Hi - I hope you can help. I am creating a search result from a keyword array passed to a param in a stored proc. But I think there must be a better way of working it. I'm passing the parameters like: @separator char(1), (usually a space) @txtkeywords varchar(500), (something like "luxury car buyers") I am creating a temp table like this : declare @Result TABLE ( listid int, score bigint, p bit, e bit, t bit , f bit, s bit, i bit, listtype varchar(1), ListStatusCode int, selected bit, CountryINT int, CurrencyID int ) Then I am iterating each word passed in @txtkeywords and inserting into @Result giving a score depending on where and how I find it. set @separator = ' ' while patindex('%' + @separator + '%' , @txtkeywords) <> 0 begin select @separator_position = patindex('%' + @separator + '%' , @txtkeywords) select @array_value = left(@txtkeywords, @separator_position - 1) select @like_text = '%' + @array_value + '%' Select @Start_text = @array_value + ' %' Select @in_text = '% ' + @array_value + ' %' Select @end_text = '% ' + @array_value -- Take each keyword and iterate for score depending on which field it has been found INSERT INTO @Result (listid, score, p, e, t, f, s, i, listtype, ListStatusCode, CountryINT, CurrencyID) SELECT List.ListId, 1000 AS rankrate, List.postflag, List.emailflag, List.phoneflag, List.faxflag, List.smsflag, List.insertflag, List.ListType, List.ListStatusCode, Company.CountryINT, list.CurrencyID FROM List with (nolock) INNER JOIN Company with (nolock) ON List.CompanyId = Company.CompanyId WHERE list.ListName Like @Start_text or list.listname like @end_text or list.listname like @in_text INSERT INTO @Result (listid, score, p, e, t, f, s, i, listtype, ListStatusCode, CountryINT, CurrencyID) SELECT List.ListId, 500 AS rankrate, List.postflag, List.emailflag, List.phoneflag, List.faxflag, List.smsflag, List.insertflag, List.ListType, List.ListStatusCode, Company.CountryINT, list.CurrencyID FROM List with (nolock) INNER JOIN Company with (nolock) ON List.CompanyId = Company.CompanyId WHERE list.ListName Like @like_text INSERT INTO @Result (listid, score, p, e, t, f, s, i, listtype, ListStatusCode, CountryINT, CurrencyID) SELECT List.ListId, 100 AS exrate, List.postflag, List.emailflag, List.phoneflag, List.faxflag, List.smsflag, List.insertflag, List.ListType, List.ListStatusCode, Company.CountryINT, list.CurrencyID FROM List with (nolock) INNER JOIN Company with (nolock) ON List.CompanyId = Company.CompanyId WHERE list.description Like @Start_text or list.description like @end_text or list.description like @in_text -- or list.description like @like_text select @txtkeywords = stuff(@txtkeywords, 1, @separator_position, '') end I perform something like 8 selects like the above for each word entered to acheive the right result set. When I have my result table - I select it out and done. It works - but doesn't seem graceful or particularly quick. Can you suggest a better approach? Many thanks Peter
parametersrank
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

pFab avatar image pFab commented ·
Update : I have moved it over into a Full Text Search engine - Just trying to work out how to return the relevancy of the word hit from FREETEXT search.
0 Likes 0 ·

0 Answers