differents result FullTextSearch Contains and like

Question

I've put the content of pdf files into a table. I've searched with

SELECT * FROM Doc WHERE Content LIKE '%blitz%

It found 78

SELECT * FROM Doc WHERE CONTAINS(Content, '"*blitz*"')

it found 72

I've opened the PDFs that the contains don't find as result, and the word blitz is in it.

Telefon / EDV / Pot. / Blitzschutz

SpenglerBlitzschutz

How can write the perfect equivalent of like '%word%'

I didn't find many help from google. And it's the first time that I want to use a Full Text Search Catalog

Answer 1

Full Text search, as the name depicts, indexes full words and is way faster than LIKE operator when searching text in large text files. LIKE operator will always scan everything in text(Including Spaces) on the other hand Full Text Search will index based on full words and will show results based on full words. You have a way to tell SQL Server which characters/words to be treated as Noise Words by using Stop List(A configurable file in system). For more information on Stop Lists: https://learn.microsoft.com/en-us/sql/relational-databases/search/configure-and-manage-stopwords-and-stoplists-for-full-text-search

Some more info about CONTAINS: CONTAINS can search for:

1. A word or phrase.
2. The prefix of a word or phrase.
3. Word forms (like superlative degree of word) example: drives, drove, driving, and driven will show when you search for drive.
4. Synonyms.

In your case I would say that you will have to use LIKE operator separately to get those 5 words, as contains won't be able to search them (Unless of course full words are specified).

Answer 2

Try rebuild FTS index https://technet.microsoft.com/en-us/library/bb326034(v=sql.105).aspx . If decadency still exist provide us exact words CONTAINS did not get.