I need to search for a text in multiple columns of my table. Right now I have WHERE 'text' in (col1, col2, col3, col4)
, but I'd need to add Wildcard operators to the text, as it should find any results that contain the text anywhere in any of these columns.
I COULD use WHERE col1 LIKE '%text%' OR col2 LIKE '%text%' OR col3 LIKE '%text%' OR col4 LIKE '%text'
, but I wanted to know if there's any way to use the Wildcard Operators with in()
or any other way to do this easier, as using a lot of OR
can quickly get confusing.
Since Stackoverflow is forcing me to explain how this question is different from Can I use wildcards in "IN" MySQL statement? : The linked question only covers multiple SEARCH TEXT OPTIONS, I need multiple COLUMN options. I DO NOT KNOW how to use the answer of the linked question for my case. I tried it and it did not work.
CodePudding user response:
You could just concatinate them with a separator.
SELECT *
FROM your_table
WHERE CONCAT_WS('|', col1, col2, col3, col4) LIKE '%text%'
In MySql 8 one could also use an EXISTS
for this
SELECT *
FROM your_table t
WHERE EXISTS (
select 1
from (
select t.col1 as col union all
select t.col2 union all
select t.col3 union all
select t.col4
) q
where col LIKE '%text%'
);
Demo on db<>fiddle here