title: SQL Server Full-Text Search for exact match with fallback
body: <p>First off there seems to be no way to get an exact match using a full-text search. This seems to be a highly discussed issue when using the full-text search method and there are lots of different solutions to achieve the desired result, however most seem very inefficient. Being I'm forced to use full-text search due to the volume of my database I recently had to implement one of these solutions to get more accurate results.</p>
  
  <p>I could not use the ranking results from the full-text search because of how it works. For instance if you searched for a movie called <code>Toy Story</code> and there was also a movie called <code>The Story Behind Toy Story</code> that would come up instead of the exact match because it found the word <code>Story</code> twice and <code>Toy</code>.</p>
  
  <p>I do track my own rankings which I call "Popularity" each time a user access a record the number goes up. I use this datapoint to weight my results to help determine what the user might be looking for.</p>
  
  <p>I also have the issue where sometimes need to fall back to a LIKE search and not return an exact match. I.e. searching <code>Goonies</code> should return <code>The Goonies</code> (most popular result)</p>
  
  <p>So here is an example of my current stored procedure for achieving this:</p>
  
  <pre><code>DECLARE @Title varchar(255)
  SET @Title = '"Toy Story"'
  --need to remove quotes from parameter for LIKE search
  DECLARE @Title2 varchar(255)
  SET @Title2 = REPLACE(@title, '"', '')
  
  --get top 100 results using full-text search and sort them by popularity
  SELECT TOP(100) id, title, popularity As Weight into #TempTable FROM movies WHERE CONTAINS(title, @Title) ORDER BY [Weight] DESC
  
  --check if exact match can be found
  IF EXISTS(select * from #TempTable where Title = @title2)
  --return exact match
  SELECT TOP(1) * from #TempTable where Title = @title2
  ELSE
  --no exact match found, try using like with wildcards
  SELECT TOP(1) * from #TempTable where Title like '%' + @title2 + '%'
  DROP TABLE #TEMPTABLE
  </code></pre>
  
  <p>This stored procedure is executed about 5,000 times a minute, and crazy enough it's not bringing my server to it's knees. But I really want to know if there was a more efficient approach to this? Thanks.</p>