CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2008full-text-search
Corey
Corey

Reputation: 398

SQL: Like vs Contains - Different Results

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.

Upvotes: 9

Views: 36345

Answers (2)

DougEC
DougEC

Reputation: 355

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

Upvotes: 3

daniloquio
daniloquio

Reputation: 3902

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.

Upvotes: 2

Related Questions