SQL: how vs Contains - different results

Question

I run two queries in a table.

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

and

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

The first request will return

 'STYCAST 50300 LV' 

And the second will return

 'STYCAST 50300 LV' 'STYCAST 2851 BLACK' 

Does anyone know why this returns more values ​​than it contains? Is there a problem with how I'm running? Thanks in advance.

Answer 1

Here's a similar post where restoring a full-text catalog seems to have solved the problem:

SQL problem: using CONTAINS () does not work, but LIKE works fine

Answer 2

CONTAINS is a completely different function; it is a predicate-based query for full-text columns; it is not a function to determine if a column contains a row.

For a running query, you can use this:

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

There you have a prefix search, and not a simple search, as you currently have.

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

---

Perhaps, the way you use it, the text “STYCAST 2851 BLACK” does not get into the results, because it has one more character than “STYCAST 50300 LV”, so this is [7 out of 17 matches] against [7 out of 16 matches]. I'm not sure, but that might explain this strange behavior.