User Id :    Password :      New Member   Forgot Password
 
Using IN clause to search in multiple columns of a table in SQL Server
Description Its possible to search a text in multiple columns of a table in SQL Server using a single IN instead of using multiple OR command.   No. of Views     1982
  Rating     5
Author Najmul Hoda   Posted On     26 Dec 2011
Tags SQL SERVER    

Sample Code   Download Code

 

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

About Author

About Author He is a .Net developer having 4+ years of experience. He loves writting technical article for .Net developers. Najmul Hoda
No Photo
 
Country India
Company BirlaSoft
Home Page www.dotnetlogix.com

Rate this article

Rating options from poor, fair, good, very good to excelent.  
 

Comments

 
 
Posted By Annonymous on 02 Sep 2011 at 01:00 PM
 
Yeah that's what I'm taiklng about baby--nice work!
 
 
 
Posted By Rajat Sharma on 25 Aug 2011 at 10:36 AM
 
Hi Najmul, its gud for avoid handy work but could u share which query is faster (my concern for performance wise). i am thankful for this article. cheers :)
 
 
     
Write your comment here.  
Comment
Verification Code   
  
    
 
Section sponsored by