1

When I search TaxIdentityNumber using contains an unexpected result is returned; the result contains numbers that are not matched to query. For example, I search using '"5689523657*"' and get 5681254870, 5458976235, etc. as result. I rebuilt the index, but get the same result. What should I try? What did I miss?

The full query:

SELECT *
FROM [Contractors] [contractor] 
INNER JOIN [ReportDates] [reportDate]
    ON [contractor].[ReportDateId] = [reportDate].[Id] 
LEFT JOIN [Persons] [tempPersons]
    ON [contractor].[PersonId] = [tempPersons].[Id] 
LEFT JOIN [Organizations] [tempOrganiztions]
    ON [contractor].[OrganizationId] = [tempOrganiztions].[Id] 
LEFT JOIN [Organizations] [tempHolding]
    ON [contractor].[HoldingId] = [tempHolding].[Id] 
WHERE
    CONTAINS(([contractor].[TaxIdentityNumber]), '"5689523657*"')

Running on SQL Server 2008 Standard Edition. Query to create full text index:

CREATE FULLTEXT CATALOG SPFTI;
GO
CREATE FULLTEXT INDEX ON [Contractors]
(FullName, TaxIdentityNumber, RelatedOrganizationFullName) 
KEY INDEX ContractorPK
ON SPFTI; 
  • 1
    How is information in [TaxIdentityNumber] stored? – cha Apr 14 '14 at 5:48
  • @cha, it's nvarchar(max) – Mark Twain Apr 14 '14 at 9:50
2

This is a known issue when using SQL Server 2008 to search for a number with more than nine digits. When using certain word-breakers, the number is stored in its scientific form e.g. nn5d68952e+009, leading to false-positive matches. One of the word-breakers affected is the neutral language (lcid 0):

-- Result includes nn5d68952e+009
SELECT display_term 
FROM sys.dm_fts_parser('"5689523657*"', 0, 0, 0);

A language that does not behave this way is us-english (lcid 1033):

-- Result includes nn5689523657
SELECT * 
FROM sys.dm_fts_parser('"5689523657*"', 1033, 0, 0);

There are several other workarounds and fixes mentioned in the KB article link above.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.