Why do CONTAINS and LIKE return different results?

Question

I have the following query. There are two possible columns that may hold the value I'm looking for, let's call them FieldA and FieldB.

If I execute this:

SELECT COUNT(1) 
FROM Table 
WHERE CONTAINS(Table.*, 'string')

I get back "0".

However, if I execute this:

SELECT COUNT(1) 
FROM TABLE 
WHERE FieldA LIKE '%string%' OR FieldB LIKE '%string%'

I get back something like 9000. I then checked and there are rows that have the word string in either FieldA.

Why does this happen? I recall that CONTAINS uses a full-text index, but I also recall that LIKE does the same, so if the problem was that the indexes are outdated, then it should fail for both of them, right?

Thanks

`LIKE` does not use a full text index. For `CONTAINS()` to find it, "string" would have to be a separate word in the text, not in the stop words list, and meet other requirements for indexing. — Gordon Linoff, Jun 18 '18 at 13:52
Does your like example work if you don't wildcard it? `LIKE 'string'` — sniperd, Jun 18 '18 at 13:54
@GordonLinoff what do you mean that it needs to meet other requirements for indexing? — Heathcliff, Jun 18 '18 at 14:02
@Heathcliff Curious about this but what does this query return to you `SELECT COUNT(1) FROM TABLE WHERE FieldA LIKE '% string %' OR FieldB LIKE '% string %'` — DhruvJoshi, Jun 18 '18 at 14:09
try `contains(Table.*,'string*')` so that you get a prefix term, 'specifies a match of words or phrases beginning with the specified text' acording to docs — Thomas Andreè Wang, Jun 18 '18 at 14:37
@Heathcliff . . . There are minimum word lengths, maximum word lengths, valid character sets, valid word separators, and possibly a bunch more options. — Gordon Linoff, Jun 19 '18 at 01:10

score 4 · Answer 1 · answered Jun 18 '18 at 14:14

I believe that CONTAINS and full text searching will only yield whole word results, so you won't match the same as LIKE '%string%'. If you want to right wildcard your CONTAINS, you must write it like:

SELECT COUNT(1) FROM Table WHERE CONTAINS(Table.*, '"string*"')

However, if you want to left wildcard, you can't! You have to store a copy of your database reversed and then do:

SELECT COUNT(1) FROM Table WHERE CONTAINS(Table.*, '"gnirts*"')

https://learn.microsoft.com/en-us/previous-versions/office/developer/sharepoint-2010/ms552152(v=office.14)

How do you get leading wildcard full-text searches to work in SQL Server?

So in the example in the question, doing a CONTAINS(Table.*, 'string') is not the same as doing LIKE '%string%' and would not have the same results.

Thanks. However I've checked and the results do show that the word 'string' appears alone and not just as a portion of another word. So, technically `CONTAINS` should be picking it up. — Heathcliff, Jun 18 '18 at 14:16
Ah, then indeed your index may be out of date. I heavily had to use full text indexes in a past job, and we would find that it would stop keeping up to date for no good reason and we would have to be delete and remake it. Can you get it to return anything at all? — sniperd, Jun 18 '18 at 14:18

Why do CONTAINS and LIKE return different results?

1 Answers1