Search code examples
mysqlsearchsql-like

MySQL LIKE Usage One Than More Columns

I want to select records from my table. I want to use this query:

select * 
from crawler.crawler_data 
WHERE name_surname, category, description LIKE "%de%";

But MySQL doesn't accept this query. I want to search all columns with LIKE. How can I write this query to search more than one column with LIKE statement?

Solution

  • You can use CONCAT:

    SELECT * FROM crawler_data WHERE CONCAT(name_surname, category, description) LIKE "%de%";

    So it is concatenating all the columns you want to check into a single column.

    There maybe times where this is unsuitable, where the string you're looking for can be found crossing the split between two columns, so giving you a false positive.

    To solve this you can use CONCAT_WS which is Concatenate with Seperator. So:

    SELECT * FROM crawler_data WHERE 
CONCAT_WS('-',name_surname, category, description) LIKE "%de%";

    Obviously, setting the seperator value (-) to being some sort of string that you will not have in the search term (de).

    Reference: MySQL Manaul