title: SQL full text search on multiple columns with AND logic and wildcard
body: <p>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.</p>
  <p>I tried with contains function with AND operator. It looks like it searches only on one column.</p>
  <p>In a table &quot;Items&quot; I have 2 column Details and Assignee.
  <a href="https://i.stack.imgur.com/bzS5B.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/bzS5B.png" alt="enter image description here" /></a></p>
  <p>When user search for &quot;doxy vive&quot; 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.</p>
  <pre><code>select Details
  from [Items]
  where CONTAINS((Details,Assignee),'&quot;doxy*&quot; AND &quot;vive*&quot;') 
  </code></pre>
  <p>This explains how to search on multiple columns but I need to do AND on multiple columns.
  <a href="https://docs.microsoft.com/en-us/sql/t-sql/queries/contains-transact-sql?view=sql-server-ver15#querying-multiple-columns-full-text-search" rel="nofollow noreferrer">https://docs.microsoft.com/en-us/sql/t-sql/queries/contains-transact-sql?view=sql-server-ver15#querying-multiple-columns-full-text-search</a></p>