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

Answer 1

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.