CODEWITHSUNDEEP
sqlsql-serverfull-text-search
xurumanga
xurumanga

Reputation: 45

Full text search across columns

Sorry for the bad post title but I couldn't summarize this better.

It's better to use an example. Say I have this simple table with two text columns (I'm leaving the other columns out).

Id    Text_1  Text_2
1     a       a b
2     c       a b

Now if I want to search for '"a" and not "b"', in my current implementation I'm getting record 1 back. I understand why this is, it's because the search condition is a match on column "Text_1", while for record 2 it's not a match on any column.

However, for the end user this may not be intuitive, as they probably mean to exclude record 1 as well most of the time.

So my question is, if I want to tell SQL Server to do the matching "across all columns" (meaning that if the "NOT" portion is found on ANY column, the record shouldn't match), is it possible?

EDIT: This is what my query would look like for this example:

SELECT Id, TextHits.RANK Rank, Text_1, Text_2 FROM simple_table
JOIN CONTAINSTABLE(simple_table, (Text_1, Text_2), '"a" and not "b"') TextHits
ON TextHits.[KEY] = simple_table.Id
ORDER BY Rank DESC

The actual query is a bit more complicated (more columns, more joins, etc) but this is the general idea :)

Thanks!

Upvotes: 1

Views: 206

Answers (1)

Eric J. Price
Eric J. Price

Reputation: 2785

The logic is going to be evaluated against each record so if you want an exclusion hit from one record in a row to cause an exclusion on the row you should use a NOT EXISTS and break out the fullText query into separate inclusionary and exclusionary parts...

SELECT  Id, 
        TextHits.RANK Rank, 
        Text_1, 
        Text_2 
FROM    simple_table
JOIN    CONTAINSTABLE(simple_table, (Text_1, Text_2), '"a"') TextHits
        ON  TextHits.[KEY] = simple_table.Id
WHERE   NOT EXISTS (SELECT  1
                    FROM    CONTAINSTABLE(simple_table, (Text_1, Text_2), '"b"') exclHits
                    WHERE   TextHits.[KEY] = exclHits.[KEY])
ORDER BY Rank DESC

Upvotes: 1

Related Questions