0

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]
3
  • What version of SQL Server are you using? Mar 24, 2015 at 20:07
  • SQL Server 2014
    – Kenshou
    Mar 24, 2015 at 20:07
  • Can you provide the fully built SQL query too? Mar 24, 2015 at 20:19

1 Answer 1

0

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
1
  • Thanks, I did query on my stoplist, "v." is actually a stopword. However I still feel this is a bit confusing. I did a query just adding "v.". This returned some results. Since "v." is a stopword, I would assume that SQL would ignore that query am I right?
    – Kenshou
    Mar 25, 2015 at 17:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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