Using multiple fields in MySQL LIKE operator

Question

I want to create a search bar in python. The search bar will search records from table of MySQL database. Table has 9 fields. I want that search bar searches from all the 9 fields and show the result. But I can use only 2 fields at most.

command = "SELECT * FROM table WHERE field1 or field2 LIKE '%PARAM%"

I Want something like this...

command = "SELECT * FROM table WHERE field1 or field2 or field3 or field4 or field5 LIKE '%PARAM%"

But multiple or's are not supported in SQL commands

So is there any way I can search from every field using LIKE Operator

Field1 like '%' OR field2 like '%' OR... ... Use SQL parameter to not repeat — Selvin
– Selvin, Commented Jun 24, 2021 at 8:44
The request you are asking for above is not too common in SQL. Having the need to check for the same value across so many might indicate a design smell. You may include your table definition above if you wish. — Tim Biegeleisen
– Tim Biegeleisen, Commented Jun 24, 2021 at 8:45
@TimBiegeleisen it makes sense as a 'free text search' across multiple fields. — JeffUK
– JeffUK, Commented Jun 24, 2021 at 8:45
I'm worried about the possibility of SQL injection in this scenario, make sure you take it into account! — JeffUK
– JeffUK, Commented Jun 24, 2021 at 8:52

JeffUK · Accepted Answer · 2021-06-24 08:49:28Z

1

You could just concatenate them

command = "SELECT * FROM table WHERE concat(field1, field2, field3, field4, field5) LIKE '%PARAM%"

You may want to join them with some separator to reduce the possibility of collisions.

command = "SELECT * FROM table WHERE concat_ws('|', field1, field2, field3, field4, field5) LIKE '%PARAM%"

edited Jun 24, 2021 at 8:49

answered Jun 24, 2021 at 8:46

JeffUK

4,2912 gold badges22 silver badges35 bronze badges

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

2 Comments

Selvin Over a year ago

Field1 ends with 'pro' and field2 starts with 'blem' and we are searching for 'problem' and we have problem... Edit: not with edited

JeffUK Over a year ago

@Selvin see edit. it still fails for 'PROB|LEM' = 'PROB','LEM', but For a search bar I don't think this would normally need to be 100%... call it a feature!

Collectives™ on Stack Overflow

Using multiple fields in MySQL LIKE operator

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related