CODEWITHSUNDEEP
sql-serversql-server-2012
SkysLastChance
SkysLastChance

Reputation: 221

Searching Multiple Columns with Multiple Values SQL

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?

Upvotes: 3

Views: 19449

Answers (5)

Thor Hovden
Thor Hovden

Reputation: 423

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

Upvotes: 0

Biju Kalanjoor
Biju Kalanjoor

Reputation: 552

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].

Upvotes: 0

user1040975
user1040975

Reputation: 440

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%'

Upvotes: 1

Rajesh Bhat
Rajesh Bhat

Reputation: 811

There is one more way...

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

Upvotes: 6

Zohar Peled
Zohar Peled

Reputation: 82474

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

Upvotes: 2

Related Questions