title: Regarding sql server table column search with PATINDEX
body: <p>i have never use <code>PATINDEX()</code> but i hard the table data can be search with <code>PATINDEX()</code>.
  often i got requirement to search multiple column of any table then i write the sql like</p>
  
  <pre><code>SELECT * FROM ADDRESS WHERE
  ((NAME LIKE 'Bill%') OR (CITY LIKE 'Bill%') OR (COMPANY LIKE 'Bill%'))
  AND
  ((NAME LIKE 'Seattle%') OR (CITY LIKE 'Seattle%') OR (COMPANY LIKE 'Seattle%'))
  </code></pre>
  
  <p><strong>so just tell me the above my sql performance will be good always? i search google to get better sql for searching multiple column of a table and found the below sql</strong></p>
  
  <pre><code>select * from YourTable
  WHERE PATINDEX('%text1%',COALESCE(field1,'') + '|' + COALESCE(field2,'') + '|'+ COALESCE(field3,'')+ '|' + COALESCE(field4,'')+ '|' + COALESCE(field9,''))&gt;0
  AND
   PATINDEX('%text2%',COALESCE(field1,'') + '|' + COALESCE(field2,'') + '|'+ COALESCE(field3,'')+ '|' + COALESCE(field4,'')+ '|' +COALESCE(field9,''))&gt;0
  </code></pre>
  
  <p>please guide me that the above sql <code>PATINDEX</code> syntax is ok for searching multiple column. if not then guide me how can i use the <code>PATINDEX</code> function to search multiple column with multiple value. thanks</p>
  
  <h2>UPDATE QS</h2>
  
  <p>you gave a link and that show me how to search multiple fields with multiple keyword. here it is</p>
  
  <pre><code>SELECT FT_TBL.ProductDescriptionID,
     FT_TBL.Description, 
     KEY_TBL.RANK
  FROM Production.ProductDescription AS FT_TBL INNER JOIN
     CONTAINSTABLE (Production.ProductDescription,
        Description, 
        '(light NEAR aluminum) OR
        (lightweight NEAR aluminum)'
     ) AS KEY_TBL
     ON FT_TBL.ProductDescriptionID = KEY_TBL.[KEY]
  WHERE KEY_TBL.RANK &gt; 2
  ORDER BY KEY_TBL.RANK DESC;
  GO
  </code></pre>
  
  <p>but the problem is it is bit complicated to understand. can u give me sql for searching multiple fields with multiple keywords with full text search.</p>
  
  <p>can i write something like this for searching multiple fields against multiple value</p>
  
  <pre><code>SELECT Title
  FROM Production.Document
  WHERE FREETEXT (Document, '(vital) OR (safety) OR (components)')
  
  SELECT *
  FROM YourTable
  WHERE CONTAINS((ProductName, ProductNumber, Color), 
  '(vital) OR (safety) OR (components)');
  </code></pre>
  
  <p><code>what is the difference between FREETEXT and CONTAINS ?? can u please explain.</code> </p>
  
  <p>thanks</p>