1

I have this table

**

--------------------------------------------------
| id    | fname       | lname      | age
--------------------------------------------------
| 1     | John        | Smith      | 20
-------------------------------------------------
| 2     | John Craig  | Smith      | 20
-------------------------------------------------- 
| 3     | John Shaw   | Smith      | 20
--------------------------------------------------

MYSQL QUERY:

select id from person where concat(fname, lname) LIKE = '%johnsmith%' - this can

select the id but if there are two words in last name like this:

select id from person where concat(fname, lname) LIKE = '%johncraigsmith%'

it will show no result.

Why? Can you help me?

woninana
  • 3,409
  • 9
  • 42
  • 66

1 Answers1

2

Because you have a space between john and craig. That would work

select id from person 
where replace(concat(fname, lname),' ','') LIKE = '%johncraigsmith%'

but that is terrible on performance BTW. Better would be

select id from person 
where lname = 'smith'
and fname = 'john craig'
juergen d
  • 201,996
  • 37
  • 293
  • 362