Searching Multiple Columns with Multiple Values SQL

Question

I know it is posible to serach multiple columns with one value.

I would like to serach 3-4 columns for 4 maybe 5 values

I want to check if any of my choosen columns have a certain value in them.

Example

Column 1 | Column 2 | Column 3 | Column 4 
         |          |          |
Hello    |          |          |            = True
         |          |          |
         | Goodbye  |          |            = True
         |          |  Hello   | Goodbye    = True
         |          |          |
         | Hello    |          |            = True
         |          |          |
         |          |  Goodbye |            = True

In the example I would like SQL to pull the data from all of the lines that have Hello or Goodbye even both in some cases.

Is there a way to do what I want?

Rajesh Bhat · Accepted Answer · 2017-03-16 07:53:20Z

7

There is one more way...

SELECT *
FROM TableName
WHERE 'Value1' IN (Col1,Col2,Col3...) OR 'Val2' in (Col1,Col2,Col3...) OR ...

answered Mar 16, 2017 at 7:53

Rajesh Bhat

8214 gold badges10 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user1040975 Over a year ago

Have yet to see this work... Logically it's sound, but having ran a similar statement you will return no records.

user1040975 Over a year ago

Only works if you know your datatypes match and you know that you have normalized data. In a perfect world, it works.

user1040975 Over a year ago

Yeah, that's why I did it the way I did. Columns had different types, so had to convert.

Zohar Peled · Accepted Answer · 2017-03-16 06:31:32Z

2

If it's only 3 or 4 columns, the simplest solution would be something like this:

SELECT *
FROM TableName
WHERE Column1 IN('Hello', 'Goodbye')
OR Column2 IN('Hello', 'Goodbye')
OR Column3 IN('Hello', 'Goodbye')
OR Column4 IN('Hello', 'Goodbye')

answered Mar 16, 2017 at 6:31

Zohar Peled

82.9k11 gold badges79 silver badges134 bronze badges

2 Comments

user1040975 Over a year ago

Sadly this and the CONTAINS() are probably your most effective/applicable solutions. I'm trying to do this as well as union the results.

user1040975 Over a year ago

Found a better way and posted it as a sln.

Danilo Körber · Accepted Answer · 2022-09-06 15:08:33Z

1

Forgot to follow with my solution: I needed to join 2 tables and search across the columns. They ****ed up and made the id of t1 a varchar, smh, and some of them had nulls so we needed to check for them lest our results were ****ed (this is why the selected answer wouldn't work for me). You don't need the aliases but if you were going deeper it helps keep things straight.

Use "+" operator to add columns to a WHERE, check for nulls, and caste if you need too.

SELECT *
 FROM Table1 AS t1
 LEFT OUTER JOIN Table2 AS t2 
     ON t1.id = t2.id 
 WHERE( ISNULL(CONVERT(VARCHAR,t1.id),'') +  ISNULL(t1.name,'') +  ISNULL(t1.desc,'') +  ISNULL(t2.company,'')) LIKE '%xbox%'

edited Sep 6, 2022 at 15:08

Danilo Körber

9402 gold badges9 silver badges32 bronze badges

answered Feb 7, 2020 at 18:00

user1040975

4396 silver badges16 bronze badges

Comments

Biju Kalanjoor · Accepted Answer · 2023-12-07 07:18:10Z

0

If you want to use wildcards in your criteria patindex is another option

Sample query

declare @SearchText nvarchar(500) 
set @SearchText = '%Biju _umar%'
declare @colSperator nvarchar(10)
// use any character for contactinating your columns , should handle this 
// in your logic
set @colSeperator = '`~`' 
select * from [ActiveProjects] 
where PATINDEX(@SearchText,CONCAT_WS(@colSeperator,[Name],[CreatedByName])) > 0

the searchText can contains wild card characters , the above query will return the contains like matching 'Biju Kumar' or any character instead on '_' like 'Biju Uumar' from columns [Name] and [Description].

answered Dec 7, 2023 at 7:18

Biju Kalanjoor

5521 gold badge6 silver badges14 bronze badges

Comments

Thor Hovden · Accepted Answer · 2024-01-16 13:17:53Z

0

I would go for the 'in' example.

There is another solutions you can sometimes consider; case.

Select truefalse=case
    when [Column 1]='Hello' then 1
    when [Column 2]='Goodbye' then 1
    when [Column 3]='Hello' and [Column 4]='Goodbye' then 1
    when [Column 2]='Hello' then 1
    when [Column 3]='Goodbye' then 1
    else 0 end
from table

answered Jan 16, 2024 at 13:17

Thor Hovden

4336 silver badges13 bronze badges

Collectives™ on Stack Overflow

Searching Multiple Columns with Multiple Values SQL

5 Answers 5

3 Comments

2 Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

3 Comments

2 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related