MySQL Search Across Columns with Concat

Using MySQL’s Concat and Concat_Ws is your friend when trying to search two joined column/field values.

This will sometimes be encountered in a simple example such as a name search, when you have two columns: 1. First Name (fname) and 2. Last Name (lname) and your search criteria is by Full Name.

So, let’s say you have a table named ‘gms’ with 2 columns, first name and last name:

fname  lname
billy beane
brian  cashman

Let’s say, you’re searching for a great general manager. You would of course search for the full name;  “billy beane”.

The sql for this would look like so:

select * from gms where concat_ws(‘ ‘,fname,lname) like “%billie beane%”;

You can also just strip the space and use ‘concat’:

select * from gms where concat(fname,lname) like “%billiebeane%”;

So, that’s about it. Using MySQL concat to search the value of 2 joined columns.

Post written by Ed Reckers

Founder and lead web development consultant at Red Bridge Internet : San Francisco WordPress Developers and Consultants.

5 Responses to “MySQL Search Across Columns with Concat”

  1. Daniel

    Thanks!, just what I was looking for

  2. kiran

    thanks a lot for this tip. Really kool.

  3. adrian

    Hello, I have tried searching multiple columns with this technique only on 3 joined tables. The problem is that when I join a fourth table the results for the same search terms aren’t the same anymore. Any help is appreciated thanks.

  4. jhonyia

    query is not working its giving error

  5. John Temoty Roca

    Thanks a lot it helps me a lot dude… Your a genius…

Leave a Reply