Match String on Multiple Columns
1 min read

Match String on Multiple Columns

Match String on Multiple Columns

TL;DR: Use concatenation, Luke!

I had to search a name for an user in a table containing the first and last names in separate columns. After a while I got tired on looking up the first_name and last_name columns one at a time and I decided to try and search both. The result is something like:

SELECT * FROM users
  WHERE LOWER(first_name || ' ' || last_name) LIKE '%laur%';

I have to do the LOWER() call because the names are provided by user input.

At first I've been worried about performance, but since this is not a recurrent query, it's acceptable (0.25s on average from a table with 100k records).

The Query can be optimised a bit more since the concatenated string is not displayed (0.2s on average):

SELECT * FROM users
  WHERE LOWER(first_name || last_name) LIKE '%laur%';

Note: MySQL has a CONCAT_WS(" ", first_name, last_name) to concatenate strings.

HTH,