building a light(small) SQL Search engine to search products database

[toptomato]

Hi,
I am attempting to build a small scale (light weight) SQL search engine (basically a stored procedure) to search through Products Table. I am passing a search string, which i break up by the ' ' (space) delimiter. next for each [value] (after breaking up) in the search string I want to be able to count the number of occurences of each [value] in the [ProductName] [ProductDescription] fields of the [Products] table. However I am confused as if its even possible. We can count the number of rows(or products) that contain the [value] in either of the [products] data table fields but i don't know if its possible to count the number of occurences of the [value] in each product. If you guys can give your thoughts as to if its possible or not or if it is then how , then that be greatly appreciated. your fellow coder
thanks in advance

 

[r937]

yes, you can count the number of occurrences, and use it as a rough measure of relevance, and even sort on it

see Simple Keyword Relevance

written using mysql syntax but the concepts are portable




r937.com | rudy.ca

 

[gmmastros]

I saw SQLDenis do this in a response about a week ago. I think it should work out pretty well for you.

Declare @Test VarChar(1000)
Declare @Search VarChar(100)

Set @Test = 'This is This, and this, and that.'
Set @Search = 'this'

Select (Len(@Test) - Len(Replace(@Test, @Search, ''))) / Len(@Search)

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom