CODEWITHSUNDEEP
mysqlsql
Michael_Panayiotou
Michael_Panayiotou

Reputation: 19

SQL Query use multiple criteria and LIKE wildcard

I'm trying to check if one of the involved person names (1,2,3 or 4) exists in my database. I tried the following query but it doesnt work as i want.

SELECT *
FROM db.cases
WHERE (inv_person_name_1 OR inv_person_name_2 OR inv_person_name_3 OR inv_person_name_4) LIKE '%something%'

I also can't use something like

SELECT *
FROM db.cases
WHERE inv_person_name_1 LIKE '%something%'
      OR inv_person_name_2 LIKE '%something%'
      OR inv_person_name_3 LIKE '%something%'
      OR inv_person_name_4 LIKE '%something%'

because it doesn't fit my program needs. I need to have only one LIKE '%something%'

Upvotes: 0

Views: 2633

Answers (2)

MPelletier
MPelletier

Reputation: 16687

Assuming you're using C# (as it is currently tagged), you need to format your query with SqlParameters:

SELECT * FROM db.cases 
WHERE inv_person_name_1 LIKE {0} 
OR inv_person_name_2 LIKE {0} 
OR inv_person_name_3 LIKE {0} 
OR inv_person_name_4 LIKE {0};

or:

SELECT * FROM db.cases 
WHERE inv_person_name_1 LIKE @name 
OR inv_person_name_2 LIKE @name 
OR inv_person_name_3 LIKE @name  
OR inv_person_name_4 LIKE @name;

In which case you need to do cmd.BindByName = true; (see better way of using a single parameter multiple times in c#)

Another alternative which is ugly, but if your LIKE will always be in the %something% format (that is, wild card before AND after), you can concatenate the names thus:

SELECT * FROM db.cases 
WHERE (inv_person_name_1 || '+' || inv_person_name_2 || '+' || inv_person_name_3 || '+' || inv_person_name_4) LIKE @name;

or

SELECT * FROM db.cases 
WHERE CONCAT_WS('+', inv_person_name_1, inv_person_name_2, inv_person_name_3, inv_person_name_4) LIKE @name;

Upvotes: 1

Crocoduck
Crocoduck

Reputation: 101

SELECT * FROM db..cases WHERE inv_person_name IN (1, 2, 3, 4) -- for integer types

SELECT * FROM db..cases WHERE inv_person_name IN ('1', '2', '3', '4') -- for varchar types

Upvotes: 0

Related Questions