Full Text Search - phrase searching and Thesaurus

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 2008 Forums

Transact-SQL (2008)

Full Text Search - phrase searching and Thesaurus

Author

Topic

chill_uk
Starting Member

5 Posts

Posted - 2015-04-21 : 09:30:04

In my FTS Thesaurus for a hardware type store I have the following :


<expansion>
<roll>
<roller>
<rolling>
</expansion>

When I search for "Bosch corner roll" using the following query :


select tblp.productid, tblp.productcode, tblp.description, KEY_TBL.RANK 
from tblProduct tblp
INNER JOIN CONTAINSTABLE(tblProduct, *, 'FORMSOF(THESAURUS,"bosch corner roll")') 
AS KEY_TBL ON tblP.ProductID = KEY_TBL.[KEY]

I get matches for anything and everything that includes the term "roller" "roll" and "rolling" and the "Bosch corner" part is totally disregarded. It seems to almost revert to a FREETEXT style search. I was hoping it would search for "Bosch corner roll", "Bosch corner roller" and "Bosch corner rolling" of course.

I have tried putting "AND" between the words to no avail. How do I get it to use the thesaurus for part of the entered phrase and not completely blow the rest of the searched term out of the water? This is for a public facing e-commerce site so we can't control what is searched for so my approach needs to kind of fit all.

I have experimented with breaking up the incoming search term into separate words but then the Thesaurus doesn't work is someone searches for "lawn mower" and I am passing "lawn" and "mower" and I want it to replace with "lawnmower".

Many Thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources