Index Server Faq
SQL Server 2000 shipped with Thesaurus capabilities which were disabled for use with the Contains predicate. They do work with FreeText however.
If you made a typo while doing a FormsOf(Inflectional search in Contains you would get this interesting message:
Syntax error occurred near 'spelling_mistake'. Expected 'INFLECTIONAL, THESAURUS' in search condition 'formsof(spelling_mistake,run)'.
Which lead me to believe that it nearly shipped. Proding around I figured out how to do it.
Microsoft then released this kb article:
And with SP 2, you will not longer get this message, now the message is:
Server: Msg 7631, Level 15, State 1, Line 1 Syntax error occurred near 'spellingmistake'. Expected 'INFLECTIONAL' in search condition 'formsof(spellingmistake,test)'.
But the thesaurus functionality still works with FreeText search. It is unsupported by Microsoft and consequently should not be used in a production environment, as it is vulnerable to the next service pack, patch, and may have unpredictable consequences.
Review this kb article to understand how to implement it.
Keep in mind that for US English modify the tseng.xml file, and for the Queen's English, modify the tsenu.xml file. These files can be found in C:\Program Files\Microsoft SQL Server\MSSQL\FTDATA\SQLServer\Config
The good news is that Thesaurus capabiltiies are planned to ship in Yukon.
In the mean time if you need a supported solution to implement thesaurus capabilities to your web site have a look at this code sample give to me by SQL Server MVP Steve Kass (skass at drew.edu).
-- Data table
create table Hilary (
Item varchar(100) primary key,
Repl varchar(1000) default space(1) not null
--note we want ''
--make big enough for replacements ...
) -- Add trailing space to each junk word
insert into Hilary(Item) values ('the ')
insert into Hilary(Item) values ('in ')
insert into Hilary(Item) values ('a ')
insert into Hilary(Item) values ('an ')
insert into Hilary(Item) values ('on ')
insert into Hilary(Item) values ('for ')
insert into Hilary(Item) values ('to ')
insert into Hilary(Item) values ('of ')
-- Add trailing space to each replacement
insert into Hilary(Item,Repl) values ('spain','(spain or espa??a) ')
insert into Hilary(Item,Repl) values ('rain','(precipitation or rain or showers) ')
-- A table of integers is needed
create table Seq ( Nbr int not null )
insert into Seq
select top 4001 0 from Northwind..[Order Details]
cross join (select 1 as n union all select 2) X
declare @i int
set @i = 1
set @i = Nbr = @i + 1
alter table Seq
add constraint pk_Seq primary key (Nbr)
--table Seq created
create function Spaces (@s varchar(8000)) returns table
as return select Nbr as Pos from Seq where substring(@s,Nbr,1) = space(1) and Nbr < len(@s)
create function Prepared_string (@s varchar(8000)) returns varchar(8000)
set @s = @s + space(1)
declare @out varchar(8000)
-- This is an undocumented trick, but I think it will
-- always work here. There are probably some safer
-- workarounds, but putting the tokens into a table and
-- rebuilding the output in a loop, a bit more slowly.
select @out = '' select @out = @out + coalesce(H.Repl,S.Item+space(1))
from ( select Pos, ltrim(rtrim(substring(@s,Pos+1, charindex(space(1),@s,Pos+1)-(Pos+1))))
as Item from dbo.Spaces(@s) ) S left join Hilary H on S.Item = H.Item
declare @x varchar(200)
set @x = 'The rain in spain falls mainly on the plain'
select left(@x,45) as StringIn, left(dbo.Prepared_string(@x),80) as StringOut
DROP FUNCTION Spaces
DROP TABLE Hilary
DROP TABLE Seq
DROP FUNCTION Prepared_string
designed by :: smilla group