Recently one of colleague shared a new way of matching the value in all the columns.
Lets understand this by an example
We have person table in which three column FirstName, LastName, MiddleName exists now suppose we need to find in all the three column if any column contain letter “A”.
simply this query can be write as mention below
SELECT DISTINCT BusinessEntityID, FirstName,LastName,MiddleName FROM [Person].[Person] WHERE FirstName like ('%a%') Or LastName like ('%a%') Or MiddleName like ('%a%') ORDER BY BusinessEntityID
Now same query can be write in following manner
SELECT DISTINCT BusinessEntityID, FirstName,LastName,MiddleName FROM [Person].[Person] WHERE FirstName + LastName + ISNULL(MiddleName,'') like ('%a%') ORDER BY BusinessEntityID
So if you see above query we have concatenate all the column and then apply like.
The important point is to remember here if a column contain NULL value then you have to convert it.
I hope this is another new way which I learnt hope it may be new for you.