SQL In() with Wildcard Operators For MULTIPLE COLUMNS

Question

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.

@diiN__________ No, since that article focuses on multiple search-text options, I need multiple column options. I wouldn't know how to use the answer of that article for my needs. Thank you, though! — alex, Dec 14 '21 at 13:56
https://stackoverflow.com/questions/20301358/mysql-look-for-the-same-string-in-multiple-columns — Shadow, Dec 14 '21 at 14:06

LukStorms · Accepted Answer · 2021-12-14T14:52:48.660

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

SQL In() with Wildcard Operators For MULTIPLE COLUMNS

1 Answers1