tsql - FullText search with CONTAINS on multiple columns and predicate - AND

Question

I have a search table with, say, 4 columns of text data to search.

I do something like this:

SELECT * FROM dbo.SearchTable
WHERE CONTAINS((co1, col2, col3, col4), 'term1 AND term2')

It looks like Contains only returns true if term1 and term2 are in the same column. Is there any way to specify that all columns should be included with an AND?

If not, my idea is to JSON all search columns and stick them into one. That way I can full text search them but still easily extract the individual columns in .NET. I'm presuming that the indexer won't have a problem with this and will dispense with the JSON characters and quotes. Is this correct?

Thanks

EDIT

Thinking about the JSON idea, the crawler would also index the property names so I'd have to rename {name}, {details}, {long_details} to something like {x1}, {x2}, {x3} to ensure they'd not be picked in a search. Hopefully if they're so short they wouldn't be indexed anyway.

EDIT2

I can create a Stoplist, based on the system Stoplist and put the property names into that.

Answer 1

This should work:

SELECT * FROM dbo.SearchTable
WHERE CONTAINS((co1, col2, col3, col4), 'term1')
AND CONTAINS((co1, col2, col3, col4), 'term2');

Alternatively, you could add a new computed column with a full text index on it. Add a column like this:

computedCol AS col1 + ' ' + col2 + ' ' + col3 + ' ' + col4

And create the full text index:

CREATE FULLTEXT INDEX ON SearchTable (computedCol LANGUAGE 1033)
KEY INDEX pk_SearchTable_yourPrimaryKeyName

Then you can do this:

SELECT * FROM dbo.SearchTable
WHERE CONTAINS(*, 'term1 AND term2')