Words that have leading decimal points are not returned by an English word breaker in SQL Server

Symptoms
In an English language word breaker, you use a full-text index to index content that contains words that have leading decimal points, such as .325, .434, .646. When you try to locate rows in the index by searching on the decimal value (for example, .325), no rows are returned.
Workaround
To work around this problem, use one of the following methods:

  • Use the neutral word breaker.
  • Put a zero in front of the decimal point when you use an English word breaker. For example, use "0.325" instead of ".325" in your search. The English word breaker correctly handles indexing and searching when it encounters leading zeros.
More information

Steps to reproduce the problem

  1. Create a full-text index to create an index that contains words that have leading decimal points, such as .325, .434, .646, and so on.

  2. Use the following full-text query to search on these values by using an English word breaker whose LCID is 1033:

    Select * from sys.dm_fts_parser('"Ring, .325 x .434 .646 Platinum"', 1033, 0,0)

    Results

    keywordgroup_idphrase_idoccurrencespecial_termdisplay_termNotes
    0x007700610073006800650072101Exact MatchRing
    0x002E003300310033102Exact Match .325–Keeps the decimal
    0x006E006E00300064003300310033102Exact Match nn0d325
    0x0078103Noise Wordx
    0x006E006E003400330038104Exact Match .434–Keeps the decimal
    0x006E006E003400330038104Exact Match nn434
    0x003000340036105Exact Match .646–Keeps the decimal
    0x006E006E00340036105Exact Match nn46
    0x007300680069006D106Exact Match Platinum


  3. Try to search on ".325" (including the decimal point):

    Select * from sys.dm_fts_parser('.325', 1033, 0,0) –Using English word breaker to specify the ".325" search term.
    Note We do not get a match.

    Results

    keywordgroup_idphrase_idspecial_termdisplay_termNotes
    0x003300310033101Exact Match325–Removes the decimal when searching and 325 <> .325, so no row returned
    0x006E006E003300310033101Exact Matchnn325


    In this example, if you enter ".325" as the search value, no rows are returned. This is because we indexed the data by keeping the decimal point, but the English word breaker removes the decimal point during the search process. Therefore, we inadvertently search on “325” instead of “.325,” and no matches are found.

    Searching a full-text index by using an English word breaker for words that have leading decimal points works correctly if we use the neutral word breaker.

  4. Run the following query by using the neutral word breaker:

    Select * from sys.dm_fts_parser('"Ring, .325 x .434 .646 Platinum"', 0, 0,0)

    Results

    keywordgroup_idphrase_idoccurrencespecial_termdisplay_termNotes
    0x007700610073006800650072101Exact Match Ring
    0x002E003300310033102Exact Match .325–Keeps the decimal
    0x006E006E00300064003300310033102Exact Match nn0d325
    0x0078103Noise Wordx
    0x002E003400330038104Exact Match .434–Keeps the decimal
    0x006E006E00300064003400330038104Exact Match nn0d434
    0x002E003000340036105Exact Match .646–Keeps the decimal
    0x006E006E00300064003000340036105Exact Match nn0d646
    0x007300680069006D106Exact Match Platinum


    Now, searching on ".325" works as expected.

    Select * from sys.dm_fts_parser('.325', 0, 0,0) –Specifying Neutral word breaker.
    Results

    keywordgroup_idphrase_idoccurrencespecial_termdisplay_termNotes
    0x002E003300310033101Exact Match.325
    0x006E006E00300064003300310033101Exact Matchnn0d325
Status
Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.
プロパティ

文書番号:3191316 - 最終更新日: 09/29/2016 22:15:00 - リビジョン: 3.0

Microsoft SQL Server 2016 Enterprise, Microsoft SQL Server 2016 Standard, Microsoft SQL Server 2016 Web, Microsoft SQL Server 2016 Express, Microsoft SQL Server 2014 Enterprise, Microsoft SQL Server 2014 Business Intelligence, Microsoft SQL Server 2014 Standard, Microsoft SQL Server 2014 Web, Microsoft SQL Server 2014 Express, Microsoft SQL Server 2012 Enterprise, Microsoft SQL Server 2012 Business Intelligence, Microsoft SQL Server 2012 Standard, Microsoft SQL Server 2012 Web, Microsoft SQL Server 2012 Express

  • KB3191316
フィードバック