partial word searches

[David Marr]

I am trying to get a CONTAINSTABLE full-text search to return partial
results. for example when someone enters NT as the criteria I want WinNT to
come up. I tried adding the * into the query, but that only seems to return
results with the search string as the prefix... ie: a search on Win* would
return Windows...

my query looks like this..

Select distinct top 501 a.name, a.drvversion, c.path, b.rank, a.DisplayCode
from catDrivers a inner join containstable(catDrivers, *, '"*6*"') b on
a.objid=b.[key] inner join sim.dbo.tbl_simgroups c on a.driverid=c.idref
where c.otype=7 order by b.Rank desc, a.name

Any ideas??

[Hilary Cotter]

SQL FTS is not designed to index word prefixes just suffixes - i.e. it will
index the word according to its stem, run, runs, ran, running, runner are
recognized as part of the word stem run. Kindly, fondly, lovely, etc are
not recognized as having any commonality and are indexed as separate items.

You might want to consider the thesaurus option.

Look at c:\program files\microsoft sql server\mssql\ftdata\sqlserver\config
you will find some files in here of the from ts???.xml. Find the one for
your language. ENU is normally for US English, and ENG is for American
English, but unfortunately MS seems to have made a mistake here and
tsenu.xml if for British English and tseng.xml if for American English. You
can edit the incorrect one for your language (i.e. for American edit
tseng.xml, for British tsenu.xml) or correct the registry setting.

For American English change
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\ContentIndexCommon\LanguageResources\O
verride\SQLServer\English (United States)\TsaurusFile to tsenu.xml

For the Queens English change

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\ContentIndexCommon\LanguageResources\O
verride\SQLServer\English (United Kingdom)\TsaurusFile to tseng.xml

Edit these your xml file like this (notice how I have removed the comment
stuff)
<XML ID="Tahoe Thesaurus">
<thesaurus xmlns="x-schema:tsSchema.xml">
<expansion>
<sub weight="0.8">Internet Explorer</sub>
<sub weight="0.2">IE</sub>
<sub weight="0.9">IE5</sub>
</expansion>
<replacement>
<pat>NT5</pat>
<pat>W2K</pat>
<sub weight="1.0">Windows 2000</sub>
</replacement>
<replacement>
<pat>bart6</pat>
<sub weight="1.0">part6</sub>
<sub weight="1.0">dart6</sub>
<sub weight="1.0">cart6</sub>
</replacement>

<expansion>
<sub weight="0.5">run**</sub>
<sub weight="0.5">jog**</sub>
</expansion>
</thesaurus>
</XML>

Then do your queries like this

select * from table2 where freetext(col1,'bart6')

in my case I have part6, bart6, and cart6 in my table
so I get this returned

pk col1 col2 col3
----------- ---------- ---------- ----------
31 cart6 NULL NULL
30 bart6 NULL NULL
29 part6 NULL NULL

AFAIK - this only works in SQL 2000.

HTH

--
Hilary
www.iisfaq.com
"David Marr" <dm...@intellution.com> wrote in message
news:unD61gHFBHA.1652@tkmsftngp02...

[John Kane]

Hilary,
Beware that although, the "thesaurus option" may work for SQL 2000, it's
not recommended nor has it been tested by Microsoft and is not supported by
Microsoft. Note, this behavior might also change in future releases of SQL
Server as well.
Regards,
John Kane
Microsoft Corp.

"Hilary Cotter" <hil...@worldnet.att.net> wrote in message
news:eYjp#SLFBHA.1352@tkmsftngp02...