CODEWITHSUNDEEP
sql-serversearchfull-text-searchfreetext
Bick
Bick

Reputation: 18551

sql server full text search - looking for an example usage of thesaurus in full text search

I am running a few selects over adventure works.
(i.e. 

SELECT *
FROM Production.Product
WHERE FREETEXT(*, 'screw washer spaner');

) I have yet to encounter a select that uses the thesaurus and displays the finding of synonyms.
How can I know if I am using this feature?
can anyone supply a select that demonstrate the usage of the thesaurus

Upvotes: 3

Views: 3188

Answers (1)

Thronk
Thronk

Reputation: 655

Here is the setup script (thanks to Itzik Ben-Gan (author Querying SQL Server)) which will allow you to use example query at bottom of thesaurus:

IF OBJECT_ID('dbo.Documents', 'table') IS NOT NULL
    DROP TABLE dbo.Documents;

CREATE TABLE dbo.Documents
(
    id  INT NOT NULL IDENTITY,
    title NVARCHAR(100) NOT NULL,
    doctype NCHAR(4) NOT NULL,
    docexcerpt NVARCHAR(1000) NOT NULL,
    doccontent VARBINARY(MAX) NOT NULL,
    CONSTRAINT PK_Documents
        PRIMARY KEY CLUSTERED(id)
);
GO
INSERT INTO dbo.Documents
(title, doctype, docexcerpt, doccontent)
SELECT N'Introduction to Data Mining', 
 N'docx',
 N'Using Data Mining is becoming more a necessity for every company 
   and not an advantage of some rare companies anymore. ',
 bulkcolumn;
GO

--Edit the thesaurus file by adding 

<expansion>    
   <sub>need</sub>    
   <sub>necessity</sub> 
</expansion>

--Run the following to reload your edited thesaurus

EXEC sys.sp_fulltext_load_thesaurus_file 1033; 
GO

And in a separate batch, execute the following command:

SELECT * 
FROM dbo.Documents
WHERE FREETEXT(doccontent,  N'FORMSOF(THESAURUS, need)');
GO

The word "need" is not in the docexcerpt, however as the synonym is loaded in the thesaurus, the row will return if the thesaurus is loaded properly. If you have issues with that, there are many StackOverflow articles and BOL entries on how to load/configure it.

Upvotes: 4

Related Questions