CODEWITHSUNDEEP
sqlcontains
hwschuur
hwschuur

Reputation: 1111

CONTAINS with multiple terms over multiple columns

I'm trying to perform a CONTAINS query with multiple terms over multiple columns, like this:

SELECT ID
FROM Table
WHERE CONTAINS((Data1,Data2,Data3), '"foo" & "bag" & "weee"')

However, this query does not behave like I want it to: I want it to return all records for which all terms appear at least once in at least one of the columns, like this:

SELECT ID
FROM Table
WHERE CONTAINS((Data1,Data2,Data3), '"foo"')
AND CONTAINS((Data1,Data2,Data3), '"bag"')
AND CONTAINS((Data1,Data2,Data3), '"weee"')

While this query returns the correct results, it needs a separate AND-clause for every term. Is there a way to express the same query with a single where-clause like in the upper example? This would be convenient when including the query in a (fixed) function.

Upvotes: 9

Views: 10146

Answers (2)

AjayDarshanala
AjayDarshanala

Reputation: 41

According to azure documentation. https://learn.microsoft.com/en-us/sql/t-sql/queries/contains-transact-sql?view=sql-server-ver16

You can query multiple columns by specifying a list of columns to search. The columns must be from the same table.

For example, the following CONTAINS query searches for the term Red in the Name and Color columns of the Production.Product table of the AdventureWorks2022 sample database

Use AdventureWorks2022;
GO
SELECT Name, Color
FROM Production.Product
WHERE CONTAINS((Name, Color), 'Red');

Upvotes: 1

Quassnoi
Quassnoi

Reputation: 425753

SQL Server once behaved this way, but it was considered a bug and "corrected".

You need to create a FULLTEXT index on a computed column:

DROP TABLE t_ft
CREATE TABLE t_ft (id INT NOT NULL,
        data1 NVARCHAR(MAX) NOT NULL, data2 NVARCHAR(MAX) NOT NULL, data3 NVARCHAR(MAX) NOT NULL,
        totaldata AS data1 + ' ' + data2 + ' ' + data3,
        CONSTRAINT pk_ft_id PRIMARY KEY (id))

CREATE FULLTEXT INDEX ON t_ft (totaldata LANGUAGE 1033) KEY INDEX pk_ft_id

INSERT
INTO    t_ft
VALUES  (1, 'foo bar', 'baz', 'text')

INSERT
INTO    t_ft
VALUES  (2, 'foo bar', 'bax', 'text')


SELECT  *
FROM    t_ft
WHERE   CONTAINS (*, 'foo AND baz')

In MySQL, on the contrary, the fulltext index searches and matches across all columns and this is a documented behavior.

Upvotes: 9

Related Questions