Using mysql concat() in WHERE clause?


Question

I would like to search my table having a column of first names and a column of last names. I currently accept a search term from a field and compare it against both columns, one at a time with

    select * from table where first_name like '%$search_term%' or 
    last_name like '%$search_term%';

This works fine with single word search terms but the result set includes everyone with the name "Larry". But if someone enters a first name then a space, then a last name, I want a narrower search result. I've tried the following without success.

    select * from table where first_name like '%$search_term%' or last_name 
    like '%$search_term%' or concat_ws(' ',first_name,last_name) 
    like '%$search_term%';

Any advice?

EDIT: The name I'm testing with is "Larry Smith". The db stores "Larry" in the "first_name" column, and "Smith" in the "last_name" column. The data is clean, no extra spaces and the search term is trimmed left and right.

EDIT 2: I tried Robert Gamble's answer out this morning. His is very similar to what I was running last night. I can't explain it, but this morning it works. The only difference I can think of is that last night I ran the concat function as the third "or" segment of my search query (after looking through first_name and last_name). This morning I ran it as the last segment after looking through the above as well as addresses and business names.

Does running a mysql function at the end of a query work better than in the middle?

1
43
11/20/2008 1:02:23 PM

Accepted Answer

What you have should work but can be reduced to:

select * from table where concat_ws(' ',first_name,last_name) 
like '%$search_term%';

Can you provide an example name and search term where this doesn't work?

84
11/19/2008 11:17:59 PM

Note that the search query is now case sensitive.

When using

SELECT * FROM table WHERE `first_name` LIKE '%$search_term%'

It will match both "Larry" and "larry". With this concat_ws, it will suddenly become case sensitive!

This can be fixed by using the following query:

SELECT * FROM table WHERE UPPER(CONCAT_WS(' ', `first_name`, `last_name`) LIKE UPPER('%$search_term%')

Edit: Note that this only works on non-binary elements. See also mynameispaulie's answer.


Licensed under: CC-BY-SA with attribution
Not affiliated with: Stack Overflow
Icon