CODEWITHSUNDEEP
mysql
Jordash
Jordash

Reputation: 3103

MySQL Search by combination of first and last name

I have a database like this:

first_name    last_name
Susan         Jones
Captain       Kirk
Luke          Skywalker

I'm trying to write a query like this:

var search = 'Luke Sky';

SELECT * FROM users WHERE first_name + last_name LIKE '%Luke Sky%'

But I don't think that syntax is correct.

Upvotes: 1

Views: 657

Answers (2)

Matt Hobbs
Matt Hobbs

Reputation: 41

You can do this in a couple of ways. Firstly you could split the search string on " " to give you separate first and last names and then you could do where first_name like '%Luke%' and last_name like '%sky% Or you could use the concat function so something like concat(first_name, " ", last name) like '%Luke sky% Personally I'd go for the first one because then it lets you match each name partially. I.e "lu sky" whereas the second way would require the first name matches fully.

Upvotes: 0

tadman
tadman

Reputation: 211590

In MySQL you combine strings with CONCAT(), not +, so it's:

SELECT * FROM users WHERE CONCAT(first_name, ' ', last_name') LIKE '%Luke Sky%'

What you ultimately want is something like:

WHERE ... LIKE ?

Then supply a placeholder value constructed in your application layer that has the requisite % values added on the start and end.

As always, remember that "first name" and "last name" are hazy concepts at best. Some cultures have family name first and others don't really have a last name at all.

Upvotes: 1

Related Questions