King Fisher">
Click here to Skip to main content
15,123,509 members
Articles / Database Development / SQL Server / SQL Server 2008R2
Tip/Trick
Posted 21 May 2015

Stats

21.1K views
10 bookmarked

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
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)

Share

About the Author

King Fisher
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



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

-பாரதியார்
Group type: Social Group

16 members


Comments and Discussions

 
GeneralMy 5 Pin
Rajesh waran16-Jul-15 2:29
professionalRajesh waran16-Jul-15 2:29 
QuestionOne Question Pin
Animesh Datta10-Jun-15 22:44
MemberAnimesh Datta10-Jun-15 22:44 
QuestionMy vote Pin
Member 1135189327-May-15 21:32
MemberMember 1135189327-May-15 21:32 
QuestionSearching Columns - Not Working Pin
treborbear26-May-15 5:17
Membertreborbear26-May-15 5:17 
AnswerRe: Searching Columns - Not Working Pin
King Fisher26-May-15 19:14
professionalKing Fisher26-May-15 19:14 
GeneralMy vote of 5 Pin
Sachin Mahandule25-May-15 20:25
professionalSachin Mahandule25-May-15 20:25 
GeneralRe: My vote of 5 Pin
King Fisher25-May-15 21:07
professionalKing Fisher25-May-15 21:07 
QuestionWorks with Oracle too! Pin
Mass Nerder22-May-15 4:15
MemberMass Nerder22-May-15 4:15 
GeneralRe: Works with Oracle too! Pin
King Fisher22-May-15 4:21
professionalKing Fisher22-May-15 4:21 
GeneralGood one !!! +5 Pin
Tadit Dash (ତଡିତ୍ କୁମାର ଦାଶ)22-May-15 1:12
protectorTadit Dash (ତଡିତ୍ କୁମାର ଦାଶ)22-May-15 1:12 
GeneralRe: Good one !!! +5 Pin
King Fisher22-May-15 1:19
professionalKing Fisher22-May-15 1:19 
GeneralRe: Good one !!! +5 Pin
Tadit Dash (ତଡିତ୍ କୁମାର ଦାଶ)22-May-15 1:32
protectorTadit Dash (ତଡିତ୍ କୁମାର ଦାଶ)22-May-15 1:32 
QuestionOne question Pin
Tridip Bhattacharjee21-May-15 22:18
professionalTridip Bhattacharjee21-May-15 22:18 
GeneralRe: One question Pin
King Fisher21-May-15 22:46
professionalKing Fisher21-May-15 22:46 
Hi Tridip, Smile | :)

I have tested on Sql server 2008 and 2012, but I have not tested yet on 2005 and 2000, i will check it and let you know soon.
Thanks,
King Fisher
நெஞ்சு பொறுக்கு திலையே-இந்த
நிலைகெட்ட மனிதரை நினைந்துவிட்டால்

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.