Regarding sql server table column search with PATINDEX

Question

i have never use PATINDEX() but i hard the table data can be search with PATINDEX(). often i got requirement to search multiple column of any table then i write the sql like

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%'))

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

select * from YourTable
WHERE PATINDEX('%text1%',COALESCE(field1,'') + '|' + COALESCE(field2,'') + '|'+ COALESCE(field3,'')+ '|' + COALESCE(field4,'')+ '|' + COALESCE(field9,''))>0
AND
 PATINDEX('%text2%',COALESCE(field1,'') + '|' + COALESCE(field2,'') + '|'+ COALESCE(field3,'')+ '|' + COALESCE(field4,'')+ '|' +COALESCE(field9,''))>0

please guide me that the above sql PATINDEX syntax is ok for searching multiple column. if not then guide me how can i use the PATINDEX function to search multiple column with multiple value. thanks

UPDATE QS

you gave a link and that show me how to search multiple fields with multiple keyword. here it is

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 > 2
ORDER BY KEY_TBL.RANK DESC;
GO

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.

can i write something like this for searching multiple fields against multiple value

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)');

what is the difference between FREETEXT and CONTAINS ?? can u please explain.

thanks

Answer 1

for fast search multiple columns you can setup Full-Text Search on sql server then your queries will look like that

SELECT * FROM ADDRESS 
WHERE Contains((name, city, company), 'Bill') and Contains(*, 'Seattle')

EDIT

Syntax of full-text search queries described here

EDIT

you can write

SELECT *
FROM YourTable
WHERE CONTAINS((ProductName, ProductNumber, Color), 
'"vital" OR "safety" OR "components"');

rows that contains any of words in any searched column are returned. check this answer

for difference between contains and freetext check Sql Server Full-Text Search Protips Part 2: CONTAINS vs. FREETEXT article