improved performace in multiple Column search with PATINDEX in SQL

I had a search textbox to filter the grid against multiple data columns in the grid.

It is too quick and easy to use the LIKE clause for immediate solution. as a result, there will be multiple LIKE '%Pattern%' in the where clause in the query. there should be great if we only have small amout of data. the perform will be deteriorated as the data volumn increase dramatically in the table.

it is much nicer to reduce the number of LIKE Clause with PATINDEX function for String search.


PATINDEX('%'+RTRIM(Ltrim(@SearchTerm))+'%',COALESCE(Field1,'') + '|' + COALESCE(Field2,'') + '|'+ COALESCE(Field3,'')+ '|' + COALESCE(Field4,''))>0

Please remember to use the '|' in string concatenation, this approach will solve the search term match with field1 + field2 or any another fields. for example if we have search term 'ABC', and field1 with value A and fields 2 with value BC. then field1 + field2 will be 'ABC', with '|' separator we should have field1 + field2 to be A|BC which will not match with search term.