CODEWITHSUNDEEP
mysqlsql
user1082754
user1082754

Reputation:

Combine two columns in SQL for WHERE clause

In my SQL, I am using the WHERE and LIKE clauses to perform a search. However, I need to perform the search on a combined value of two columns - first_name and last_name:

WHERE customers.first_name + customers.last_name LIKE '%John Smith%'

This doesn't work, but I wondered how I could do something along these lines?

I have tried to do seperate the search by the two columns, like so:

WHERE customers.first_name LIKE '%John Smith%' OR customers.last_name LIKE '%John Smith%'

But obviously that will not work, because the search query is the combined value of these two columns.

Upvotes: 25

Views: 53442

Answers (4)

user22465386
user22465386

Reputation: 1

I have six div each div have individual id , if one id is passed that particular div will change the color if button is submitted.

Upvotes: 0

vic
vic

Reputation: 450

try this:

SELECT * 
FROM customers 
WHERE concat(first_name,' ',last_name) like '%John Smith%';

reference:
MySQL string functions

Upvotes: 2

scott
scott

Reputation: 583

I would start with something like this:

WHERE customers.first_name LIKE 'John%' AND customers.last_name LIKE 'Smith%'

This would return results like: John Smith, Johnny Smithy, Johnson Smithison, because the percentage sign is only at the end of the LIKE clause. Unlike '%John%' which could return results like: aaaaJohnaaaa, aaaSmithaaa.

Upvotes: 3

Dmytro Shevchenko
Dmytro Shevchenko

Reputation: 34591

Use the following:

WHERE CONCAT(customers.first_name, ' ', customers.last_name) LIKE '%John Smith%'

Note that in order for this to work as intended, first name and last name should be trimmed, i.e. they should not contain leading or trailing whitespaces. It's better to trim strings in PHP, before inserting to the database. But you can also incorporate trimming into your query like this:

WHERE CONCAT(TRIM(customers.first_name), ' ', TRIM(customers.last_name)) LIKE '%John Smith%'

Upvotes: 45

Related Questions