|
The full-text search as used in the stored procedure DSC_Product_Search looks for ALL matching words (via the CONTAINS keyword).
If the search text contains 'noise words' (also known as stop-words) such as 'ON' then this causes an error in SQL and it seems no results are returned.
You could amend the SP from:
WHERE CONTAINS(p.*,' + QUOTENAME('FORMSOF(INFLECTIONAL,' + QUOTENAME(dbo.DSC_Escape(dbo.DSC_Escape(Value, N'\'), N';'), '"') + ')', '''') + ')
to:
WHERE FREETEXT(p.*,' + QUOTENAME('FORMSOF(INFLECTIONAL,' + QUOTENAME(dbo.DSC_Escape(dbo.DSC_Escape(Value, N'\'), N';'), '"') + ')', '''') + ')
but this will return all matching items where ANY of the words match.
You can also amend the SP to not use FORMSOF(INFLECTIONAL... (note that INFLECTIONAL finds all of the tenses of a word) and instead use "AND" or "NEAR" keywords to look for matches:
Example:
CONTAINS(p.*,'clip AND on AND butane AND regulator')
NOTE THAT THE 'SEARCH' METHOD IN PRODUCT.CS may be amended so that it does not pass keywords to the SP as comma-delimited. We have amended it to pass space-delimited keywords which our (amended) SP changes to use the 'near' keyword (or its '' shortcut representation). That seems to then return what we are looking for.
LATEST
If you are using SQL 2008 and you ensure that 'compatability mode' is set to 2008, you can issue a TSQL command to turn OFF stop-word checking, e.g.
ALTER FULLTEXT INDEX ON [DSC_PRODUCT] SET STOPLIST OFF
and this will also then allow results to come back where previously they would not.
Turn it back on again using e.g. ALTER FULLTEXT INDEX ON [DSC_PRODUCT] SET STOPLIST SYSTEM
|