SQL Full Text Thesaurus Issue - 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 2008 Forums

Transact-SQL (2008)

SQL Full Text Thesaurus Issue

Author

Topic

chill_uk
Starting Member

5 Posts

Posted - 2015-04-09 : 07:15:58

I have setup a custom Thesaurus for use with SQL Full Text Index (2008). For example I have the following terms :


	<expansion>
            <sub>400v</sub>
            <sub>400volt</sub>
            <sub>400 volt</sub>
            <sub>415v</sub>
            <sub>415volt</sub>
            <sub>415 volt</sub>
            <sub>3 phase</sub>
            <sub>3phase</sub>
            <sub>three phase</sub>
         </expansion>

When I issue a search for "415 volt" I am getting matches on records with "3" in the data, not just "3 phase" - example query below :


select tblp.productid, tblp.productcode, tblp.description, KEY_TBL1.RANK 
from tblProduct tblp
INNER JOIN FREETEXTTABLE(tblProduct, description, '"415 volt"') AS KEY_TBL1 ON tblP.ProductID = KEY_TBL1.[KEY] 
ORDER BY KEY_TBL1.RANK DESC

It is returning items that don't have the word "phase" in at all and just have "3"! I have had to take all numbers out of the stop list as we sell a lot of technical items that will be searched for by size / voltage etc.

It's like the expansion is allowing just one word match, when I want it to only allow whole phrase match for the substitution.

I'm tearing what's left of my hair out!

chill_uk
Starting Member

5 Posts

Posted - 2015-04-13 : 06:48:50

OK so I worked out I could use the Thesaurus with CONTAINSTABLE and that has got me further as results seem a little easier to contain - however now having issues with replacement in the Thesaurus.

I have the following section :


	<replacement>
            <pat>mower</pat>
            <pat>mowers</pat>
            <pat>lawnmowers</pat>
            <sub>lawnmower</sub>
        </replacement>

Using the following query I get :


SELECT * FROM sys.dm_fts_parser ('FORMSOF(THESAURUS,"mowers")', 1033, 0, 0)

this result :


0x006C00610077006E0077006D006F007700650072	1	1	1	Exact Match	lawnwmower	4	mowers

So it looks like it has worked? But using the query below to try and return some results gives me nothing at all as if the replacement isn't happening?


select tblp.productid,tblp.productcode,tblp.description,webinclude,KEY_TBL1.RANK from tblProduct tblp
INNER JOIN CONTAINSTABLE(tblProduct, *, N'FORMSOF(THESAURUS,"mowers")') AS KEY_TBL1 ON tblP.ProductID = KEY_TBL1.[KEY] 
ORDER BY KEY_TBL1.RANK DESC

Same for "mower" and "lawnmowers" as the search term. Also tried setting up the replacement with and without plurals. When I search just for "lawnmower" (as I want the replacement to do) the query works perfectly.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources