SQL full text search on multiple columns with AND logic and wildcard

Question

I have 3 searchable columns. When user enters more than 1 word I have to search for a row which has all the words (AND) and it can be matched in any of the 3 columns. All the words should be searched with wildcard (word*). How can I do this in SQL full text search.

I tried with contains function with AND operator. It looks like it searches only on one column.

In a table "Items" I have 2 column Details and Assignee.

When user search for "doxy vive" I need to search in both the columns with prefix and AND. This is what I tried and did not work. It works with OR condition.

select Details
from [Items]
where CONTAINS((Details,Assignee),'"doxy*" AND "vive*"')

This explains how to search on multiple columns but I need to do AND on multiple columns. https://docs.microsoft.com/en-us/sql/t-sql/queries/contains-transact-sql?view=sql-server-ver15#querying-multiple-columns-full-text-search

Please show sample data, expected results and your attemtp (all as formatted text). — Dale K

Thomas Thomas · Accepted Answer · 2020-08-25T09:22:45

I had the same problem, I dont know if its the best solution, but I solved it like this:

select Details from [Items] where CONTAINS(*,'"vive*"') AND CONTAINS(*,'"doxy*"')

SQL full text search on multiple columns with AND logic and wildcard

1 Answers