SQL: Like vs Contains - Different Results

Question

I'm running two queries on a table.

SELECT MSDS FROM dbo.MSDSSearch3 WHERE CONTAINS(MSDS, 'STYCAST')

And

SELECT MSDS FROM dbo.MSDSSearch3 WHERE MSDS like '%STYCAST%'

The first query will return

'STYCAST 50300 LV'

And the second will return

'STYCAST 50300 LV'
'STYCAST 2851 BLACK'

Does anyone know why the like would return more values than the contains? Is there a problem with how I'm running contains? Thanks in advance.

Answer 1

Here's a similar post, where rebuilding the fulltext catalog seemed to solve the problem:

SQL Problem: Using CONTAINS() doesn't work, but LIKE works fine

Answer 2

CONTAINS is a totally different function, it is a predicate based query for full-text columns; it is not a function to determine if a column contain a string in it.

For the query you are running, you could use this:

SELECT MSDS FROM dbo.MSDSSearch3 WHERE CONTAINS(MSDS, '"STYCAST*"')

There you have a prefix search, instead of a simple_term search like you currently have.

More details: http://msdn.microsoft.com/en-us/library/ms187787.aspx

---

Maybe in the way you are using it the text 'STYCAST 2851 BLACK' don't fall into results because it have one more character than 'STYCAST 50300 LV', so it is a [7 of 17 match] vs a [7 of 16 match]. I'm not sure but that could explain this strange behavior.