CODEWITHSUNDEEP
sql-serverfull-text-searchcontains
unubar
unubar

Reputation: 506

CONTAINS in full-text search does not return all results

I am using full-text search in SQL Server 2008. The following query

select * From MyTable where contains( *, 'FLOW AND VALVE')

returns two rows:

1. FLOW CONTROL VALVE
2. FLOW VALVE

but the following query 

select * From MyTable where contains( *, '"FLOW AND VALVE"'))

returns only one row:

1. FLOW CONTROL VALVE

Why doesn't the second query return the second row?

Upvotes: 5

Views: 2915

Answers (1)

Martin Smith
Martin Smith

Reputation: 453940

If you are on SQL Server 2008 you can get some clues from running

SELECT * 
FROM sys.dm_fts_parser('FLOW AND VALVE',1033,0,0)

SELECT * 
FROM sys.dm_fts_parser('"FLOW AND VALVE"',1033,0,0)

CONTAINS( *, 'FLOW AND VALVE') is interpreted as two <simple_term> searches joined together with a boolean condition. i.e. CONTAINS( *, 'FLOW') AND CONTAINS( *, 'VALVE')

CONTAINS( *, '"FLOW AND VALVE"')) is interpreted as a phrase search with the "And" ignored as a noise word.

As to why the second one matches "FLOW CONTROL VALVE" but not "FLOW VALVE" From this article

Although it ignores the inclusion of stopwords, the full-text index does take into account their position.

so essentially the presence of the stop word acts as a wildcard word match.

Upvotes: 5

Related Questions