MySQL: Searching multiple columns at once without LIKE

Have you ever had reason to search across multiple columns at the same time in MySQL? Maybe while responding to a data-table search from the frontend? - Yea me too.

You or your ORM probably generate a query like this:

SELECT
    *
FROM `addresses`
WHERE `address_line_1` LIKE "%NY%"
    OR `address_line_2` LIKE "%NY%"
    OR `city` LIKE "%NY%"
    OR `street_name` LIKE "%NY%"

And then when you have reason to add another condition, such that it has to apply to the query separate from the search conditions, you do have to come up with something like this:

SELECT
    *
FROM `addresses`
WHERE `customer_id` = 1
AND (
    `address_line_1` LIKE "%NY%"
    OR `address_line_2` LIKE "%NY%"
    OR `city` LIKE "%NY%"
    OR `street_name` LIKE "%NY%"
)

The more you update the query, adding joins for example and wanting to also include some columns from the join into the searchable fields, the more this query gets complicated.

Using MySQL LOCATE function

Mysql provides a function called LOCATE - This function returns the position of the first occurrence of a given substring. You can checkout other string functions such as POSITION.

With the LOCATE function, we can cleanup the first query to look something like this:

SELECT
    *
FROM `addresses`
WHERE LOCATE('NY', `address_line_1`) > 0
    OR LOCATE('NY', `address_line_2`) > 0
    OR LOCATE('NY', `city`) > 0
    OR LOCATE('NY', `street_name`) > 0
    OR LOCATE('NY', `street_name`) > 0

The LOCATE function returns the position of the first occurence of the string, in this case NY. And it is case-insensitive.

You are going to say... Well that ain't much of a difference is that?

Simplyfing with Mysql CONCAT_WS

Now the juicy part 😍 - CONCAT_WS

The MySQL CONCAT_WS is a variant of the CONCAT function but can accept a separator. So take for example, to return the full address as a column from our addresses table, we can have a query like this:

SELECT
    CONCAT_WS(' ', `address_line_1`, `address_line_2`, `city`, `street_name`, `street_name`) AS `full_address`
FROM `addresses`

Combining the power of CONCAT_WS and LOCATE we can have a relatively easy search without using a LIKE. Our previous example will then look somewhat like this:

SELECT
    *
FROM `addresses`
WHERE LOCATE('NY', CONCAT_WS(' ', `address_line_1`, `address_line_2`, `city`, `street_name`, `street_name`)) > 0

With this approach, you can add or remove columns that are searchable really easy. Infact you could make it dynamic by accepting searchable columns from your frontend or API.

On performance, the LOCATE method is also slightly performant compared to the LIKE.

Let me know what you think on this approach!