Select 2 concatenated columns in mysql disregarding all spaces

Question

Reference: LIKE query sql not working in concatenated values with space

Sample table: **

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

MYSQL QUERY:

SELECT CONCAT(fname,' ',lname) FROM people WHERE CONCAT_WS(first_name,'',last_name) LIKE "%JohnCraigDeSmith%"

it will show no result.

How can I build an efficient query for this?

replace CONCAT_WS with CONCAT in your query – Praveen D Oct 14 '13 at 04:45 — Praveen D, Oct 14 '13 at 04:45

score 0 · Accepted Answer · edited Oct 14 '13 at 08:04

0

SELECT CONCAT(fname, ' ', lname) 
     FROM people 
WHERE CONCAT(REPLACE(fname, ' ', ''), 
REPLACE(lname, ' ', '')) LIKE "%JohnCraigDeSmith%"

edited Oct 14 '13 at 08:04

peterm

91,357
15
148
157

answered Oct 14 '13 at 04:40

pushOk

290
1
8

Select 2 concatenated columns in mysql disregarding all spaces

1 Answers1