1

When I try to use multiple search terms in a CONTAINS clause I get no results.

SELECT *
FROM dbo.Address
WHERE CONTAINS([Address].*,'"Detroit"') AND CONTAINS([Address].*,'"48226"')
OPTION(QUERYTRACEON 9481)

SELECT *
FROM dbo.Address
WHERE CONTAINS([Address].*, ' "Detroit" AND "48226" ')
OPTION(QUERYTRACEON 9481)

The first query returns almost 20k rows. The second, which should be logically the same returns nothing. When I try the same test on my 2012 server, it returns the same number of rows, so I'm pretty sure it isn't syntax.

My version is Microsoft SQL Server 2014 (SP1-CU6) (KB3144524) - 12.0.4449.0 (X64) Apr 13 2016 12:41:07 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.3 (Build 9600: )

There is no stopword list set up for this fulltext index.

The QUERYTRACEON 9481 forces the optimizer to use the cardinality estimator for SQL Server 2012 - this solves another issue we have had with FULLTEXT indexes and TOP in 2014, but makes no difference in this case; the queries return the same number of rows with or without it.

1

Turns out these are not logically the same; the second requires the results to be in the same column, while the first is across all columns

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.