SQL Server full text search contains not working as expected

Question

I have created a table in Azure SQL Server with a full text index. I used this code to create the full text index:

use [MyDb]

create fulltext catalog full_text_catalog_foods;
go

create fulltext index 
on dbo.foods(food_name,[description])
key index PK_Foods_foodId on full_text_catalog_foods;

In my table I have one food i.e. "Tea with Milk and Sugar". So I am trying to search this result by below query. but it return empty result set.

declare @filters nvarchar(250) = 'r',
        @q nvarchar(150) = 'tea with milk';

set @q = '"'+@q+'*"';

select 
    f.[Id]
    ,f.[food_name]
    ,f.[info_id]
    ...
    ...
    --few more column
from 
    dbo.Foods f
where 
    contains(f.food_name, @q) and f.model = @filters;

I have tried above query and also my original query like below. But both query returns empty result.

declare @Offset int = 0,
    @Limit int = 50,
    @filters nvarchar(250) = 'r',
    @q nvarchar(150) = 'tea with milk';

set @q = '"'+@q+'*"';
select 
    f.[Id]
    ,f.[food_name]
    ,f.[info_id]
    ...
    ...
    --few more column
from dbo.Foods f
where contains(f.food_name,@q) and f.model = @filters
order by f.Id
offset @Offset rows
fetch next @Limit rows only;

Edit

I have three record in foods table with food_name = "Tea with Milk and Sugar" and it's model = r. When I use below code, it returns two records of name "Tea with Milk and Sugar".

declare @q nvarchar(150) = 'tea with milk and sugar';
set @q = '"'+@q+'"'; -- here removed *
…
…

I am not getting why this is not working. Can anyone help me solve this?

user_1996783 · Answer

I can't give you an indepth reason, but I'm quite sure this is caused by noise word confusion. Both the words "with" and "and" would be treated like noise words (not indexed), but when using the asterisk (prefix term) suddenly "with" is not treated like a noise word.

My guess is that this is happening:

indexed text:
    Tea (noise) Milk (noise) Sugar

search for "tea with milk":
    tea (noise) milk <- this should generate hits

search for "tea with milk*":
    tea* with* milk* <- "with" is no longer noise. No hits.

You could test this by just removing the asterisk and this should then return the expected result.

Another option to try would be to disable noise words.

ALTER FULLTEXT INDEX ON table
  SET STOPLIST OFF;

You might also want to do rebuild of the index:

ALTER FULLTEXT CATALOG REBUILD;

I fully understand that this is not a full solution, or even a really satisfactory answer, but at least it might move you forward.

SQL Server full text search contains not working as expected

1 Answers