Click here to Skip to main content
15,867,308 members
Articles / Database Development / SQL Server / SQL Server 2008R2
Tip/Trick

To Search Data in Multiple Columns using 'IN' Clause

Rate me:
Please Sign up or sign in to vote.
4.87/5 (25 votes)
21 May 2015CPOL 27.5K   10   14
To search data in multiple columns using 'IN' clause

Introduction

Let me show you how to search a record from multiple columns in a table by using Simple 'IN' Clause in SQL Server.

Using the Code

  1. Create a table with some dummy records to  show the demonstration:
    SQL
    create table tbl_test
    (id int identity(1,1),
    column1 nvarchar(50),
    coulmn2 nvarchar(50),
    coulmn3 nvarchar(50))-- Create a table 
    
    insert into tbl_Test (column1,coulmn2,coulmn3) values
    ('Griff','Serjey','Maciej'),
    ('King','Fisher','Ajay'),
    ('Paul','Griff','Serjey'),
    ('King','Fisher','Griff')--Inserting some Dummy Records
  2. Now, I'm going to search the value where the data is 'Griff'. In the above table, there are 3 rows that contain 'Griff'.
    SQL
    select *from tbl_test where 'Griff' IN (column1,coulmn2,coulmn3)
  3. By executing the above query, we will get the resultset like below:
    id   column1    coulmn2    coulmn3
    1    Griff      Serjey    Maciej
    3    Paul       Griff     Serjey
    4    King       Richard   Griff

The query will check all the mentioned columns where the input data is available by using the simple 'IN' clause.

Yeah, it's very simple, but I hope it's a very helpful tip.

History

  •  Created and published on May 22nd, 2015

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Software Developer Motherson Sumi INfotech and Designs Limited
India India
I'm Graduated with B.Tech in Information Technology at year of 2012.
2013'June-2015'June :Worked as Web Developer in Avail Technologies, Madurai
2015 -till Date :Working as software Engineer in Motherson Sumi INfotech and Designs Limited,Chennai
Technical Skill : Sql Server, C#, JavaScript, Jquery, Asp.Net



நெஞ்சு பொறுக்கு திலையே-இந்த
நிலைகெட்ட மனிதரை நினைந்துவிட்டால்

-பாரதியார்
This is a Social Group

16 members

Comments and Discussions

 
GeneralMy 5 Pin
Rajesh waran16-Jul-15 1:29
professionalRajesh waran16-Jul-15 1:29 
QuestionOne Question Pin
Animesh Datta10-Jun-15 21:44
Animesh Datta10-Jun-15 21:44 
QuestionMy vote Pin
Member 1135189327-May-15 20:32
Member 1135189327-May-15 20:32 
QuestionSearching Columns - Not Working Pin
treborbear26-May-15 4:17
treborbear26-May-15 4:17 
AnswerRe: Searching Columns - Not Working Pin
King Fisher26-May-15 18:14
professionalKing Fisher26-May-15 18:14 
GeneralMy vote of 5 Pin
Sachin Mahandule25-May-15 19:25
professionalSachin Mahandule25-May-15 19:25 
GeneralRe: My vote of 5 Pin
King Fisher25-May-15 20:07
professionalKing Fisher25-May-15 20:07 
thanks Sachin.

Big Grin | :-D
நெஞ்சு பொறுக்கு திலையே-இந்த
நிலைகெட்ட மனிதரை நினைந்துவிட்டால்

QuestionWorks with Oracle too! Pin
Mass Nerder22-May-15 3:15
Mass Nerder22-May-15 3:15 
GeneralRe: Works with Oracle too! Pin
King Fisher22-May-15 3:21
professionalKing Fisher22-May-15 3:21 
GeneralGood one !!! +5 Pin
Tadit Dash (ତଡିତ୍ କୁମାର ଦାଶ)22-May-15 0:12
protectorTadit Dash (ତଡିତ୍ କୁମାର ଦାଶ)22-May-15 0:12 
GeneralRe: Good one !!! +5 Pin
King Fisher22-May-15 0:19
professionalKing Fisher22-May-15 0:19 
GeneralRe: Good one !!! +5 Pin
Tadit Dash (ତଡିତ୍ କୁମାର ଦାଶ)22-May-15 0:32
protectorTadit Dash (ତଡିତ୍ କୁମାର ଦାଶ)22-May-15 0:32 
QuestionOne question Pin
Tridip Bhattacharjee21-May-15 21:18
professionalTridip Bhattacharjee21-May-15 21:18 
GeneralRe: One question Pin
King Fisher21-May-15 21:46
professionalKing Fisher21-May-15 21:46 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.