Compare value with concatenated fields in where clause

Question

Say I want to search for a user, 'Richard Best'. Is it possible to compare the full name is concatenated first name and last name? I do not have a full name field.

select * from users where last_name + ' ' + first_name like '%richa%'

I am using Mysql

Does this answer your question? [Using mysql concat() in WHERE clause?](https://stackoverflow.com/questions/303679/using-mysql-concat-in-where-clause) — codeMonkey, Dec 31 '20 at 21:41

score 10 · Accepted Answer · answered Feb 24 '11 at 18:18

10

These are equivalent:

select * from users where concat(last_name,' ',first_name) like '%richa%'

select * from users where concat_ws(' ',last_name,first_name) like '%richa%'

This might also work:

select * from users where last_name like '%richa%' or first_name like '%richa%'

answered Feb 24 '11 at 18:18

awm

6,526
25
24

Two propositions are not equivalent : With "Richard Gasquet", the search with "Richard G" won't work with your second solution because "Richard G" doesn't match firstname nor lastname ! – nlassaux Oct 26 '14 at 15:17
@Nico401 I have no idea what you're talking about. I stand by my claim that `concat(A,' ',B)` `concat_ws(' ',A,B)` are equivalent. If, on the other hand, you're trying to match `like '%Richard G%'` using the non-concat syntax, well then that's not going to work too well, duh. – awm Oct 27 '14 at 02:05
I'm ok for concat and concat_ws equivalence. But concatenation and your third proposition (with OR) are not equivalent. If you write the lastname and a part of the firstname, concat solution will work although the OR solution won't. – nlassaux Oct 28 '14 at 21:13
The answer is in two sections. Section one: "These are equivalent". Section two: "This might also work". There was never any suggestion that section two is equivalent to section one. – awm Oct 29 '14 at 03:41
It works in some precise cases, but I agree with you so. Take my post as a precision. – nlassaux Oct 29 '14 at 15:20

score 5 · Answer 2 · edited May 23 '17 at 10:30

5

Take a look at this thread.

Using mysql concat() in WHERE clause?

edited May 23 '17 at 10:30

Community

1
1

answered Feb 24 '11 at 18:19

Rajesh Chamarthi

18,568
4
40
67

score 0 · Answer 3 · edited Feb 24 '11 at 18:22

0

select * from users where (first_name + ' ' + last_name) like '%richa%'

edited Feb 24 '11 at 18:22

RichardTheKiwi

105,798
26
196
262

answered Feb 24 '11 at 18:21

jmlove

13
2

score 0 · Answer 4 · edited Oct 05 '20 at 11:01

0

In laravel Eloquent you can use whereRaw("concat(first_name,' ',last_name) LIKE %$search%")

edited Oct 05 '20 at 11:01

Suraj Rao

29,388
11
94
103

answered Oct 05 '20 at 10:59

Sharif

31
5

Compare value with concatenated fields in where clause

4 Answers4