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

User · Answer

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.

User · Answer

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.

User · Answer

There s a few things that could get in the way - is your data clean   It could be that you have spaces at the end of the first name field  which then means you have two spaces between the firstname and lastname when you concat them  Using trim first name  trim last name  will fix this - although the real fix is to update your data   You could also this to match where two words both occur but not necessarily together  assuming you are in php - which the  search term variable suggests you are    whereclauses array     terms   explode       search term   foreach   terms as  term         term   mysql real escape string  term        whereclauses      CONCAT first name       last name  LIKE    term        sql    select   from table where    sql    implode   and     whereclauses

User · Answer

you can do that  work in mysql  probably other SQL too   just try this   select   from table where concat     first name last name       like    search term

User · Answer

There s a few things that could get in the way - is your data clean   It could be that you have spaces at the end of the first name field  which then means you have two spaces between the firstname and lastname when you concat them  Using trim first name  trim last name  will fix this - although the real fix is to update your data   You could also this to match where two words both occur but not necessarily together  assuming you are in php - which the  search term variable suggests you are    whereclauses array     terms   explode       search term   foreach   terms as  term         term   mysql real escape string  term        whereclauses      CONCAT first name       last name  LIKE    term        sql    select   from table where    sql    implode   and     whereclauses

User · Answer

SELECT   concat ws     first name last name  AS whole name FROM users HAVING whole name LIKE    search term        is probably what you want

User · Answer

You can try this   select   FROM table where  concat first name       last name      search term

User · 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

User · Answer

SELECT   concat ws     first name last name  AS whole name FROM users HAVING whole name LIKE    search term        is probably what you want

User · Answer

There s a few things that could get in the way - is your data clean   It could be that you have spaces at the end of the first name field  which then means you have two spaces between the firstname and lastname when you concat them  Using trim first name  trim last name  will fix this - although the real fix is to update your data   You could also this to match where two words both occur but not necessarily together  assuming you are in php - which the  search term variable suggests you are    whereclauses array     terms   explode       search term   foreach   terms as  term         term   mysql real escape string  term        whereclauses      CONCAT first name       last name  LIKE    term        sql    select   from table where    sql    implode   and     whereclauses

User · 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

User · Answer

SELECT   concat ws     first name last name  AS whole name FROM users HAVING whole name LIKE    search term        is probably what you want

User · Answer

You can try this   select   FROM table where  concat first name       last name      search term

User · Answer

To Luc   I agree with your answer  although I would like to add that UPPER only works on non-binary elements  If you are working with say an AGE column  or anything numeric  you will need to perform a CAST conversion to make the UPPER function work correctly   SELECT   FROM table WHERE UPPER CONCAT WS      first name  last name  CAST age AS CHAR   LIKE UPPER    search term       Forgive me for not responding to Luc s answer directly but for the life of me I could not figure out how to do that  If an admin can move my post  please do so

User · 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

User · Answer

SELECT   concat ws     first name last name  AS whole name FROM users HAVING whole name LIKE    search term        is probably what you want

User · Answer

There s a few things that could get in the way - is your data clean   It could be that you have spaces at the end of the first name field  which then means you have two spaces between the firstname and lastname when you concat them  Using trim first name  trim last name  will fix this - although the real fix is to update your data   You could also this to match where two words both occur but not necessarily together  assuming you are in php - which the  search term variable suggests you are    whereclauses array     terms   explode       search term   foreach   terms as  term         term   mysql real escape string  term        whereclauses      CONCAT first name       last name  LIKE    term        sql    select   from table where    sql    implode   and     whereclauses

User · Answer

you can do that  work in mysql  probably other SQL too   just try this   select   from table where concat     first name last name       like    search term

User · Answer

To Luc   I agree with your answer  although I would like to add that UPPER only works on non-binary elements  If you are working with say an AGE column  or anything numeric  you will need to perform a CAST conversion to make the UPPER function work correctly   SELECT   FROM table WHERE UPPER CONCAT WS      first name  last name  CAST age AS CHAR   LIKE UPPER    search term       Forgive me for not responding to Luc s answer directly but for the life of me I could not figure out how to do that  If an admin can move my post  please do so

User · 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

[php] Using mysql concat() in WHERE clause?

Examples related to php

Examples related to mysql