title: SQL Server full text search contains not working as expected
body: <p>I have created a table in Azure SQL Server with a full text index. I used this code to create the full text index:</p>
  
  <pre><code>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;
  </code></pre>
  
  <p>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.</p>
  
  <pre><code>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;
  </code></pre>
  
  <p>I have tried above query and also my original query like below. But both query returns empty result. </p>
  
  <pre><code>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;
  </code></pre>
  
  <blockquote>
    <p>Edit</p>
  </blockquote>
  
  <p>I have three record in foods table with <code>food_name</code> = "Tea with Milk and Sugar" and it's <code>model = r</code>. When I use below code, it returns two records of name "Tea with Milk and Sugar".</p>
  
  <pre><code>declare @q nvarchar(150) = 'tea with milk and sugar';
  set @q = '"'+@q+'"'; -- here removed *
  …
  …
  </code></pre>
  
  <p>I am not getting why this is not working. Can anyone help me solve this?</p>