9

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.

Martin Smith
  • 438,706
  • 87
  • 741
  • 845
Corey
  • 398
  • 1
  • 4
  • 18
  • What version of SQL Server? If 2008 you can get some visibility into the parser with `SELECT * FROM sys.dm_fts_parser('"STYCAST 2851 BLACK"', 1033, 0,0)` I can't see any reason why the strings would be treated differently though. – Martin Smith Oct 07 '11 at 14:48
  • 3
    Interesting question, are you sure that both values are exactly the same? as in no leading / trailing whitespace characters. That could cause this issue as the CONTAINS would match exactly your text whereas the LIKE would match your text + anything either side of it – Purplegoldfish Oct 07 '11 at 14:50
  • 11
    Sounds like a case where your fulltext catalog is out of date. You might want to try rebuilding it with an [ALTER FULLTEXT CATALOG](http://msdn.microsoft.com/en-us/library/ms176095.aspx). – Joe Stefanelli Oct 07 '11 at 14:52
  • I thought of that, and ran a like 'STYCAST%', with the same result. The server is running Microsoft SQL Server 2008. I'll keep messing around with it, and let you know if I see anything out of the ordinary. – Corey Oct 07 '11 at 14:53
  • 1
    @CoreyB - In 2008 you can also query `sys.dm_fts_index_keywords` to see what is indexed. – Martin Smith Oct 07 '11 at 14:55
  • Thanks Joe, after I got ahold of a system administrator rebuilding the fulltext catalog worked. The query is running smoothly now! – Corey Oct 07 '11 at 15:38
  • @Corey: Glad that worked out for you. – Joe Stefanelli Oct 07 '11 at 16:34
  • possible duplicate of [SQL Problem: Using CONTAINS() doesn't work, but LIKE works fine](http://stackoverflow.com/questions/588025/sql-problem-using-contains-doesnt-work-but-like-works-fine) – jessegavin Jan 23 '12 at 20:04

2 Answers2

3

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

Community
  • 1
  • 1
DougEC
  • 357
  • 1
  • 6
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.

daniloquio
  • 3,822
  • 2
  • 36
  • 56
  • Just tested this solution, with the same result. SELECT MSDS FROM dbo.MSDSSearch3 WHERE CONTAINS(MSDS, '"STYCAST*"') Still returned the one value. – Corey Oct 07 '11 at 15:08
  • Sad to read that; Then it should be an outdated catalog, but you said you already updated it. It is very strange. – daniloquio Oct 07 '11 at 15:10
  • What if you run this: SELECT MSDS FROM dbo.MSDSSearch3 WHERE CONTAINS(MSDS, 'STYCAST 2851 BLACK') ? – daniloquio Oct 07 '11 at 15:12
  • Sorry, no I haven't updated yet, I should have been more specific. Currently in the process of getting a hold of the server administrator. Hopefully that will solve the issue. – Corey Oct 07 '11 at 15:13
  • As for the second question, if I run the query with the full contains, I get the error: Msg 7630, Level 15, State 3, Line 1 Syntax error near '2851' in the full-text search condition 'STYCAST 2851 BLACK'. – Corey Oct 07 '11 at 15:18