title: SQL Server Full Text Search around numbers and underscores
body: <p>Using SQL Server 2012 (in general using SQL Server 2008 R2 up to SQL Server 2016)</p>
  
  <p>This question is a more specific re wording of <a href="https://stackoverflow.com/questions/48788921/sql-server-full-text-index-unexpected-results?noredirect=1#comment84585533_48788921">SQL-Server Full Text Index Unexpected results</a>. Please see here for how we have got to this point and what has been tried already.</p>
  
  <p>I am re-posting now we have found the specific fault. Many thanks to <a href="https://stackoverflow.com/users/1587819/honeybadger">@HoneyBadger</a>. </p>
  
  <p>His help has been invaluable getting to this point.</p>
  
  <p>Table structure:</p>
  
  <pre><code>CREATE TABLE TestFullTextSearch (Id INT NOT NULL, AllText NVARCHAR(400))
  
  CREATE UNIQUE INDEX test_tfts ON TestFullTextSearch(Id)
  
  CREATE FULLTEXT CATALOG ftcat_tfts
  
  CREATE FULLTEXT INDEX ON TestFullTextSearch(AllText)
      KEY INDEX test_tfts ON ftcat_tfts
      WITH CHANGE_TRACKING AUTO, STOPLIST OFF
  </code></pre>
  
  <p>Data:</p>
  
  <pre><code>INSERT INTO TestFullTextSearch 
  VALUES (1, ' 123_456 789 '), (2, ' 789 123_456 '),
         (3, ' 123_456 ABC '), (4, ' ABC 123_456 ')
  </code></pre>
  
  <p><strong>Please note this data is purely to demonstrate the issue, and is not indicative of a live data set.  Our live data sets can be over 500,000 rows, with paragraphs of data in a single field being searched - hence using full text searches.</strong></p>
  
  <p><strong>Select 1:</strong> Results as expected</p>
  
  <pre><code>SELECT *
  FROM TestFullTextSearch
  WHERE CONTAINS (AllText, '"123*"')
  
  Id          AllText
  ----------- ------------
  1            123_456 789
  2            789 123_456
  3            123_456 ABC
  4            ABC 123_456
  </code></pre>
  
  <p><strong>SELECT 2:</strong> Misses row 2 in the result set</p>
  
  <pre><code>SELECT *
  FROM TestFullTextSearch
  WHERE CONTAINS (AllText, '"123_*"')
  
  Id          AllText
  ----------- ------------
  1            123_456 789
  3            123_456 ABC
  4            ABC 123_456
  </code></pre>
  
  <p><strong>SELECT 3:</strong> Only returns row 2</p>
  
  <pre><code>SELECT *
  FROM TestFullTextSearch
  WHERE CONTAINS (AllText, '"123\_*"')
  
  Id          AllText
  ----------- ------------
  2            789 123_456
  </code></pre>
  
  <p><strong>Conclusion:</strong> Searching for a number string suffixed with an underscore fails if preceding word is a number string.</p>
  
  <p><strong>Problem:</strong> Our clients use full text search and expect a result to be forth coming around part numbers and catalogue references, which may or may not be in a section of text including other number strings.  Full text search does not seem to support this in a consistent manner. </p>
  
  <p>Any help gratefully received.</p>
  
  <p><strong>NB: This issue does not happen on SQL SERVER 2008, but does on 2012+</strong></p>
  
  <p>I have also tried switching to the older version of FTS parser. Testing with</p>
  
  <pre><code>SELECT * FROM sys.dm_fts_parser (' "789 123_456" ',1033,0,0)
  SELECT * FROM sys.dm_fts_parser (' "789 123_456" ',2057,0,0)
  </code></pre>
  
  <p>I had with current parser:
  <a href="https://i.stack.imgur.com/mluc6.png" rel="noreferrer"><img src="https://i.stack.imgur.com/mluc6.png" alt="enter image description here"></a></p>
  
  <p>And after reverting to the legacy parser:
  <a href="https://i.stack.imgur.com/4P0G5.png" rel="noreferrer"><img src="https://i.stack.imgur.com/4P0G5.png" alt="enter image description here"></a></p>
  
  <p>So it has had an effect, however I'm still getting the same results.</p>
  
  <p>Are there any other differences in the full text search between 2008 &amp; 2012 that could be having this effect?</p>