Words that have leading decimal points aren't returned by an English word breaker in SQL Server
This article helps you resolve the problem where a search by using an English word breaker doesn't return words that have leading decimal points in SQL Server 2017 on Windows, SQL Server 2016, 2014, and 2012.
Original product version: SQL Server
Original KB number: 3191316
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
, and .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.
Steps to reproduce the problem
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.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
keyword group_id phrase_id occurrence special_term display_term Notes 0x007700610073006800650072 1 0 1 Exact Match Ring 0x002E003300310033 1 0 2 Exact Match .325 Keeps the decimal 0x006E006E00300064003300310033 1 0 2 Exact Match nn0d325 0x0078 1 0 3 Noise Word x 0x006E006E003400330038 1 0 4 Exact Match .434 Keeps the decimal 0x006E006E003400330038 1 0 4 Exact Match nn434 0x003000340036 1 0 5 Exact Match .646 Keeps the decimal 0x006E006E00340036 1 0 5 Exact Match nn46 0x007300680069006D 1 0 6 Exact Match Platinum 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 don't get a match.
Results
keyword group_id phrase_id occurrence special_term display_term Notes 0x003300310033 1 0 1 Exact Match 325 Removes the decimal when searching and 325 <> .325, so no row returned 0x006E006E003300310033 1 0 1 Exact Match nn325 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.
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
keyword group_id phrase_id occurrence special_term display_term Notes 0x007700610073006800650072 1 0 1 Exact Match Ring 0x002E003300310033 1 0 2 Exact Match .325 Keeps the decimal 0x006E006E00300064003300310033 1 0 2 Exact Match nn0d325 0x0078 1 0 3 Noise Word x 0x002E003400330038 1 0 4 Exact Match .434 Keeps the decimal 0x006E006E00300064003400330038 1 0 4 Exact Match nn0d434 0x002E003000340036 1 0 5 Exact Match .646 Keeps the decimal 0x006E006E00300064003000340036 1 0 5 Exact Match nn0d646 0x007300680069006D 1 0 6 Exact Match Platinum Now, searching on
.325
works as expected.SELECT * FROM sys.dm_fts_parser('.325', 0, 0,0) -- Specifying Neutral word breaker.
Results
keyword group_id phrase_id occurrence special_term display_term Notes 0x002E003300310033 1 0 1 Exact Match .325 0x006E006E00300064003300310033 1 0 1 Exact Match nn0d325