Skip to content

Match String on Multiple Columns

October 16th, 2017 - Software(1 min)

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,

Share on
Reddit
Linked in
Whatsapp

A little experiment: