CODEWITHSUNDEEP
phpmysqlstringconcatenation
matthy
matthy

Reputation: 8334

Mysql: search fullname in a splitted name table

I am looking for a method that you can look for a full name in a splitted collumn table

I tried: for example with "michael peter johnson"

select firstName,middleName,lastName 
from staff 
where concat(firstName, ' ', middlename, ' ', lastname) Like "%michael peter johnson%"

that works but if the name is "michael johnson" it does not work cause the concat name created: "michael (double space)johnson" So the concat creates two spaces and that does not match.

Does anyone know an alternative way to have a solution for this?

Edit: the idea is that the string "michael peter johnson" is an user input field. so it can not be splitted up in 3 seperate strings a that is not the idea of this search bar

Edit2: I also noticed that if the middlename is "NULL" and that the result of concat(firstName, ' ', middlename, ' ', lastname) with a NU:: is NULL so it would never find it.

Any solution for that?

Thx Matthy

Upvotes: 0

Views: 2431

Answers (4)

vee
vee

Reputation: 38645

Might not be the most efficient solution but should work for your requirement: 

select firstName, midlename, lastname 
from staff
where replace(concat(ifnull(firstName, ''), ifnull(middlename, ''), ifnull(lastname, '')), ' ', '') = replace('michael peter johnson', ' ', '');

Upvotes: 0

echo_Me
echo_Me

Reputation: 37233

if you serach with michael johnson . do this

     select name, midle, last 
     from staff
     where concat(name, ' ', midle, ' ', last) Like "%michael%johnson%"

here demo to play with

EDIT:

 select firstName,middleName,lastName 
 from table1
 where (firstName Like "%michael%" AND lastName  LIKE "%johnson%" )

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269883

You could do this:

select firstName, middleName, lastName 
from staff 
where concat(firstName, ' ', middlename, ' ', lastname) Like "%michael%peter%johnson%"

But I think you want:

select firstName, middleName, lastName 
from staff 
where firstName like '%michael%' and
      middleName like '%peter%' and
      lastName like '%johnson%';

Or because your database values seem to have spaces, perhaps just:

select firstName, middleName, lastName 
from staff 
where trim(firstName) = 'Michael' and
      trim(middleName) = 'Peter' and
      trim (lastName) = 'Johnson';

EDIT:

Or, you could do:

where concat(trim(firstName), ' ', trim(middlename), ' ', trim(lastname)) Like concat('%', 'michael peter johnson', '%')

Upvotes: 2

manuskc
manuskc

Reputation: 794

Replace is one more option:

select firstName,middleName,lastName 
from staff 
where replace(concat(firstName, ' ', middlename, ' ', lastname), '  ', ' ') Like "%michael peter johnson%"

Upvotes: 2

Related Questions