Using IN clause to search in multiple columns of a table in SQL Server
Sometimes we need to search a text in multiple columns of one or more table. In that case we use multiple OR clause to perform such operation.
For example if we want to find all employees records whose first name or last name or middle name is Najmul. For that we will write below code.
DECLARE @SearchText VARCHAR(20)
SET @SearchText='Najmul'
Select * FROM tblEmployee WHERE FName=@SearchText OR LName=@SearchText OR MName=@SearchText
To avoid this handy work of writting multiple ORs, we case use a single IN clause to search text in multiple column.
Here is the code
DECLARE @SearchText VARCHAR(20)
SET @SearchText='Najmul'
Select * FROM tblEmployee WHERE @SearchText IN (FName,LName,MName)
Isnt' is simple. Enjoy