MySQL: Searching multiple columns at once without LIKE
Tags: sql, mysql
Published: 2023-01-23 13:26:46

---
title: MySQL: Searching multiple columns at once without LIKE
published: true
description: 
tags: #sql #mysql
# cover_image: https://dev-to-uploads.s3.amazonaws.com/uploads/articles/mpv2004u5jrywn1qkwmp.png
# Use a ratio of 100:42 for best results.
# published_at: 2023-01-17 10:24 +0000
---

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:
```sql
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:
```sql
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](https://dev.mysql.com/doc/refman/8.0/en/string-functions.html#function_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:

```sql
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](https://dev.mysql.com/doc/refman/8.0/en/string-functions.html#function_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:

```sql
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:

```sql
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!