Sql-server – full text phrase-search with stop word

full-text-searchsql server

I would like SQL Server to search a phrase "centos 8" for example. The exact match.

But since number "8" is considered a stop word, it only searches for "centos".

Even if I use quotes

 select [KEY] FROM CONTAINSTABLE(myTable, myColumn, '"centos 8"')

How can I make it search for a phrase without disabling the stop words feature completely? (very useful in other cases)

Best Answer

You don't need to disable the STOPWORDS / STOPLIST feature completely, rather you can just remove that specific word "8" from it if you want the search to always be able to match it.

Microsoft's Docs ALTER FULLTEXT STOPLIST explain how to remove words from the default STOPLIST. And for general information on managing STOPLISTS you can read there docs Configure and Manage Stopwords and Stoplists for Full-Text Search as well.

Related Solutions

Sql-server – get similar Full Text Search functionality without using Full-Text Search

Native full-text can handle all of the features you've mentioned.

Ranking/relevance, weighting
Word inflection and expansion
Customisable noise words and thesaurus

Whether or not it will be the best solution for your particular domain is a tough call. Rather than attempt to hand-roll an alternative, spike a proof of concept using SQL FTS and repeat the exercise with one or more open source alternatives.

Lucene is the obvious contender to compare and comes with the "Stack Overflow search is now 81% less crappy" endorsement of the SO team.

Postgresql – Full Text Search With PostgreSQL

This is not really a use case for full text search because full text relies on stemming the text and parsing the chunks into tokens. As you can see from keywords, '580h' is parsed as its own word because there's no language in which '580' is a "stem" of '580h'. You'd probably be better off with regular expression matching.

Here's a query that I worked up for you:

SELECT id, title 
  FROM stickers WHERE
    (title ~* '580')
      AND
    (title ~* 'case')
ORDER BY id

Best Answer

Related Solutions

Sql-server – get similar Full Text Search functionality without using Full-Text Search

Postgresql – Full Text Search With PostgreSQL

Related Question