SQL Server 2014 – Inconsistent Results from Full Text Search Engine

full-text-searchsql serversql server 2014

I'm working on an application that makes use of the Full Text Search Engine from SQL Server. In order to convert the user input into readable parameters for SQL, i'm using This very useful FTS query converter. Now, the issue at hand:

We have a record that has the following set of keywords:

Bockelman v. MCI Worldcom, Inc.

When the user types that on the application, the converter will work out the logic and send the following parameters to CONTAINSTABLE:

   FORMSOF(INFLECTIONAL, Bockelman) AND FORMSOF(INFLECTIONAL, v) AND FORMSOF(INFLECTIONAL, MCI) AND FORMSOF(INFLECTIONAL, Worldcom) AND FORMSOF(INFLECTIONAL, Inc)

However, the query returned no results. I noticed that the converter removes the periods from both "v." and "Inc.", so I performed a direct query on the database adding those missing periods. Still, no results.

If I remove "v." from the query. It returns a number of results. At this point, I thought that the "v." was being treated as a stopword of some sort. However, when I performed another query…

John v. Smith

It returned a number of results with the "v." included on the query. This feels a bit inconsistent for me. I am probably not using the FTS Engine correctly, but I'm hoping to find some guidance here.

Thanks!

EDIT:

Here's the fully built query:

    SELECT o.OpinionDocumentId
                                       FROM CONTAINSTABLE(OpinionDocuments, Text, 'FORMSOF(INFLECTIONAL, Bockelman) AND FORMSOF(INFLECTIONAL, v) 
    AND FORMSOF(INFLECTIONAL, MCI) AND FORMSOF(INFLECTIONAL, Worldcom) AND FORMSOF(INFLECTIONAL, Inc)') AS KEY_TBL
                                       LEFT JOIN dbo.OpinionDocuments AS o ON o.OpinionDocumentId = KEY_TBL.[KEY]
                                       LEFT JOIN dbo.OpinionDocumentPlaintiffs AS p ON p.OpinionDocument_OpinionDocumentId = KEY_TBL.[KEY]
                                       LEFT JOIN dbo.OpinionDocumentDefendants AS d ON d.OpinionDocument_OpinionDocumentId = KEY_TBL.[KEY]

Best Answer

Took me a little while to see what now seems obvious. I can't seem to replicate your experience with John v. Smith on 2012, but if we check out the default system stopwords...

select * from sys.fulltext_system_stopwords
WHERE stopword = 'v.'

The result is

stopword    language_id
v.          1053

So it seems the stoplist is the problem. Let's try a blank one and see what happens:

CREATE TABLE [dbo].[fts_table](
    [name] [nvarchar](50) NULL,
    [id] [int] IDENTITY(1,1) NOT NULL,
  CONSTRAINT [PK_fts_table] PRIMARY KEY CLUSTERED 
  (
    [id] ASC
  )
GO

INSERT INTO [dbo].[fts_table]
           ([name])
     VALUES
           ('John v. Smith'), ('Bockelman v. MCI Worldcom, Inc.')
GO

--Pretend I scripted out the fulltext index creation here 

CREATE FULLTEXT STOPLIST [modified_stoplist]
AUTHORIZATION [dbo];
GO

ALTER FULLTEXT INDEX ON [fts_Table]
SET STOPLIST = modified_stoplist;
GO

Then the query now returns the results desired:

SELECT  *
 FROM CONTAINSTABLE(dbo.fts_Table, name, 
'FORMSOF(INFLECTIONAL, ''Bockelman'') 
AND FORMSOF(INFLECTIONAL,''v'') 
AND FORMSOF(INFLECTIONAL, ''MCI'') 
AND FORMSOF(INFLECTIONAL, ''Worldcom'') 
AND FORMSOF(INFLECTIONAL, ''Inc'')') 
   AS KEY_TBL

Returning...

KEY     RANK
1       32
Related Question