I have used SQL Server FREETEXTTABLE function to search in a table column based on the user entered words like a search engine and return best matching rows.
Table column will contain many questions and user will type something in textbox (in any order) and based on what he has typed I need to auto populate the search page.
I have used FREETEXTTABLE for it. But it's not working in some cases.
If I type 'what' it does not return anything.
DECLARE @query VARCHAR(50) = 'what'
SELECT TOP 10 Questions
FROM tblQuestion tq
INNER JOIN FREETEXTTABLE(tblQuestion, Questions, @query) ft ON (tq.ID = ft.[Key])
ORDER BY ft.Rank DESC
but if I type 'what is' it returns the 10 records.
DECLARE @query VARCHAR(50) = 'what is'
SELECT TOP 10 Questions
FROM tblQuestion tq
INNER JOIN FREETEXTTABLE(tblQuestion, Questions, @query) ft ON (tq.ID = ft.[Key])
ORDER BY ft.Rank DESC
I also tried CONTAINS and FREETEXT.
SELECT *
FROM tblQuestion
WHERE FREETEXT (Questions, 'what')
Even this query returned zero rows.
But this below query returned few rows.
SELECT *
FROM tblQuestion
WHERE FREETEXT (Questions, 'what is')
You are probably the victim of stop lists. Make sure all words are included in index with following query:
SELECT * FROM sys.dm_fts_index_keywords(DB_ID('YourDB'), OBJECT_ID('tblQuestion'))
Note that:
A stopword can be a word with meaning in a specific language, or it can be a token that does not have linguistic meaning. For example, in the English language, words such as "a," "and," "is," and "the" are left out of the full-text index since they are known to be useless to a search.
If you want to include all words, even those marked as useless, use following code:
ALTER FULLTEXT INDEX ON tblQuestion SET STOPLIST = OFF