Sql FREETEXTTABLE not returning result

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

Hi,
I have used SQL FREETEXTTABLE 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 its not working in some cases.

What I have tried:

If I type 'what' it does not return anything.

SQL

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.

SQL

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.

SQL

SELECT * FROM tblQuestion WHERE FREETEXT (Questions,'what')

Even this query returned zero rows.
But this below query returned few rows.

SQL

SELECT * FROM tblQuestion  WHERE FREETEXT (Questions,'what is')

I want it to return even if there is single word typed by user.
Anyone knows the solution please help.

Thanks.

Posted 1-Sep-16 0:21am

Member 12586674

Updated 7-Sep-16 18:38pm

v2

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

vani suresh · Accepted Answer · 2016-09-07T18:38:00

Hi ,

In sql server have a Some stoplist of words there.

Here is the query to check for list of stop lists

SQL

SELECT ssw.stopword, slg.name
      FROM sys.fulltext_system_stopwords ssw
      JOIN sys.fulltext_languages slg
      ON slg.lcid = ssw.language_id
      WHERE slg.lcid =1033

Here is the link for more detail
stop words list .

Becoz of the stoplist you are getting any rows.

Thanks
:)