Request

DECLARE @SearchWord nvarchar(30) SET @SearchWord = N'with' SELECT * FROM [dbo].[Sentences] WHERE CONTAINS((Sentence), @SearchWord)

returns 0 results instead of N-thousands.

How to set the processing of the request reserved words?

    2 answers 2

    Slightly supplement the answer given by participant Alex .

    When creating a full-text index, as indicated in the documentation , you can specify a list of stop words with the option

     STOPLIST [ = ] { OFF | SYSTEM | stoplist_name }

    it also states that

    If STOPLIST is not specified, SQL Server links the system full-text list of stop words to the index.

    If OFF and SYSTEM are not satisfied for some reason, you can create your own list of stop words:

     create fulltext stoplist SentencesStoplist;

    Adding the necessary words to it:

     alter fulltext stoplist SentencesStoplist add 'or' language 'English'; alter fulltext stoplist SentencesStoplist add 'and' language 'English';

    Or to create on the basis of the existing (including the system one):

     create fulltext stoplist SentencesStoplist from system stoplist;

    Throwing / adding certain words:

     alter fulltext stoplist SentencesStoplist drop 'with' language 'English'; alter fulltext stoplist SentencesStoplist add 'stop' language 'English';

    And then assign the use of the created list of stop words to the full-text index:

     alter fulltext index on dbo.Sentences set stoplist = SentencesStoplist;
    • Thank you very much for the detailed addition. Marked your answer correct. - Alex

    Collected the answer "on crumbs". In addition to its own list of stop words (which may not). There is a (!) List of system stop words, even for a neutral language in the directory settings, as in my case. Request to the list of system stop words:

     SELECT stopword FROM sys.fulltext_system_stopwords ssw WHERE language_id = 1033;

    You can remove it from the directory with the command:

     ALTER FULLTEXT INDEX ON [dbo].[Таблица] SET STOPLIST = OFF

    After that, the reserved words (and, or, not, any, from, etc.) will also be returned upon request, including full-text search.