Menu
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?
866
MySQL CONCAT() Function The CONCAT() function adds two or more expressions together.
We can use a literal in CONCAT Function. A literal is a number, character, or date that includes the SELECT statement.
We can append a string of data to an existing data of a field by using concat function of MySQL. Here we are not updating or replacing existing data with a new one, we are just adding the string at the end(or at the beginning ) of the field data. Joining strings by using CONCAT and handling NULL value.
In Microsoft SQL server, you use the addition arithmetic operator (+) to concatenate string values. Besides using spaces for string concatenation, MySQL provides two other functions that concatenate string values: CONCAT and CONCAT_WS. The MySQL CONCAT function takes one or more string arguments and concatenates them into a single string.
The concat function will not return a column value. You can't generate a dynamic column name for the where clause in MySQL. There are a number of Stack Overflow articles to that effect. Normally, I would arrange my data so that there was some sort of date or timestamp associated with the row, rather than using date specific columns.
To concatenate two or more quoted string values, you place the string next to each other as the following syntax: SELECT 'MySQL ' 'String ' 'Concatenation'; Code language: SQL (Structured Query Language) (sql)
If any argument is NULL, the CONCAT function returns a NULL value. The following statement concatenates two quoted strings: MySQL and CONCAT. SELECT CONCAT ('MySQL', 'CONCAT'); Code language: SQL (Structured Query Language) (sql)
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?
194
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.
26
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
