CODEWITHSUNDEEP
sql-serverfull-text-search
Nico
Nico

Reputation: 23

SQL Server Full Text Search not returning expected results

I installed full-text search for SQL Server, created a catalog and index. I am sure that there are proper records in the database, but FTS functions doesn't return any result.

For example:

select * from tablename where contains(title, 'baycan');
select * from tablename where contains(title, '"*baycan*"')

This is catalog and index image:

enter image description here

Upvotes: 0

Views: 1568

Answers (1)

Thom A
Thom A

Reputation: 95534

It appears you are misunderstanding CONTAINS and LIKE, they are not equivalent operators. From CONTAINS (Transact-SQL):

CONTAINS can search for:

  • A word or phrase.
  • The prefix of a word or phrase.
  • A word near another word.
  • A word inflectionally generated from another (for example, the word drive is the inflectional stem of drives, drove, driving, and driven).

A word that is a synonym of another word using a thesaurus (for example, the word "metal" can have synonyms such as "aluminum" and "steel").

Emphasis my own.

CONTAINS does not support a leading wildcard, only a trailing one. As a result, (as I say in my comment) for a column (title) with the value 'abaycant' the expression WHERE title LIKE '%baycan%' will return TRUE, however, CONTAINS(title,'baycan') will not.

If you have the value 'baycant' then LIKE 'baycan%' and CONTAINS(title,'baycan*') (and LIKE '%baycan%') would all return TRUE; the former 2 would also be SARGable too (the one in parenthesis would not be).

If the value were 'best baycant' then both CONTAINS(title,'baycan*') and LIKE '%baycan%' would return TRUE, but only the former would be SARGable.

This is also further supported later in the argument section in relation to the phrase parameter (the second parameter) when using it as a prefix (there is not option in the arguments for suffix or "in the middle of"):

<prefix_term>

Specifies a match of words or phrases beginning with the specified text. Enclose a prefix term in double quotation marks ("") and add an asterisk (*) before the ending quotation mark, so that all text starting with the simple term specified before the asterisk is matched. The clause should be specified this way: CONTAINS (column, '"text*"'). The asterisk matches zero, one, or more characters (of the root word or words in the word or phrase). If the text and asterisk are not delimited by double quotation marks, so the predicate reads CONTAINS (column, 'text*'), full-text search considers the asterisk as a character and searches for exact matches to text*. The full-text engine will not find words with the asterisk (*) character because word breakers typically ignore such characters.

When <prefix_term> is a phrase, each word contained in the phrase is considered to be a separate prefix. Therefore, a query specifying a prefix term of "local wine*" matches any rows with the text of "local winery", "locally wined and dined", and so on.

FREETEXT will not change this. FREETEXT (Transact-SQL):

When FREETEXT is used, the full-text query engine internally performs the following actions on the freetext_string, assigns each term a weight, and then finds the matches:

  • Separates the string into individual words based on word boundaries (word-breaking).
  • Generates inflectional forms of the words (stemming).
  • Identifies a list of expansions or replacements for the terms based on matches in the thesaurus.

Again, emphasis my own.

A value like 'abaycant' doesn't have any word boundaries, so FREETEXT(title, 'baycan') will not work. If you need a leading wildcard, due to the need to search within a word, you cannot utilise a Full Text Search, as an FTS indexes the words, not the characters.

Upvotes: 6

Related Questions