CODEWITHSUNDEEP
mysqlsql-like
woninana
woninana

Reputation: 3481

LIKE query sql not working in concatenated values with space

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?

Upvotes: 1

Views: 725

Answers (1)

juergen d
juergen d

Reputation: 204924

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'

Upvotes: 2

Related Questions