Monday, March 05, 2012 11:44 PM
for eg. search word is "PC is not working" my goal is to show top 10 matching rows and here is what i am planning to do:
1. all rows having exact match of "PC is not working"
2. all rows having having "PC" or "not" or "working"
and here is my SQL statement:
Select Top 10 * from Table1 INNER JOIN CONTAINSTABLE(Table1, *,@P2, LANGUAGE 1033 ) as Table1_FTS_1 on (Table1.RecId = Table1_FTS_1.[key])
@P2=N'(ISABOUT ("PC is not working" WEIGHT(1.0) , formsof(inflectional, "PC") WEIGHT(0.5), formsof(inflectional, "not") WEIGHT(0.5), formsof(inflectional, "working") WEIGHT(0.5)))'
ORDER BY Table1_FTS_1.[rank] DESC
Observe that i am giving full weigh to "PC is not working" and rest half weight. Is there a better way to achieve this? Will i get any undesired results? I tested on my db and looks good.
Tuesday, March 06, 2012 3:07 AMModerator
Hi Santosh BSK,
I have demonstrated the scenario based on your description. One thing you may pay attention to is that there is a noise word ‘is’ in your condition “PC is not working”. In this case, record with with “PC X not working” will be treated as “PC is not working”. Here, ‘X’ can be any word. You can have a try to insert rows such as “PC X not working”, “PC test not working”. Both of these rows will be outputed with the same rank as “PC is not working”.
It is not recommended to include noise word in condition. If you really want to match the condition as it is, you can stop the noize word by the statement below in SQL Server 2008 or later:
ALTER FULLTEXT INDEX ON Table1 SET STOPLIST OFF;
For SQL Server 2005, you are required to clean the noise-word files by manual, which are located in the $SQL_Server_Install_Path\Microsoft SQL Server\MSSQL.1\MSSQL\FTDATA\ directory by default. For each language, there is an individual noise-word file. Please choose a right file according to your scenario. You must repopulate the full-text catalogs before the changes will take effect:
EXEC sp_fulltext_catalog ‘YourCatalogName', 'start_full';
TechNet Subscriber Support
If you are TechNet Subscription user and have any feedback on our support quality, please send your feedback here.
TechNet Community Support
Tuesday, March 06, 2012 4:36 PMThanks for the reply. I am aware of the issues you mentioned. Apart from that hope there are no chances i get invalid rows i.e. always i will get exact match rows on the top then everything else.
Friday, March 09, 2012 12:30 AM
are following same?
@P2='("pc not working" or formsof(inflectional, "pc") or formsof(inflectional, "not") or formsof(inflectional, "working"))' @P2=N'(ISABOUT ("PC not working" WEIGHT(1) , formsof(inflectional, "PC") WEIGHT(1), formsof(inflectional, "not") WEIGHT(1), formsof(inflectional, "working") WEIGHT(1)))'