How to use ILIKE in postgres with two columns

user_6898726 0
Viewed 1125

I have two columns which are called surname and name.

For example I have 3 users

Zuckerberg Mark
Gates Bill
Jobs Steve

If my input is only one phrase such as "Ma" or "Mark" or "Gate" etc. my query works well, but if i enter and surname and name I got an empty result

select
       u.surname,
       u.name,
       u.avatar
from users u
where (surname ilike '%Mark Zuckerberg%' or name ilike '%Mark Zuckerberg%')
order by surname, name

How can I rebuild my query for this goal?

postgresql sql
1 Answers

Sorry my previous answer had a typo, but this works:

select
       u.surname,
       u.name,
       u.avatar
from users u
where ((u.surname ||' '|| u.name) ilike '%Mark Zuckerberg%' or (u.name ||' '||u.surname) ilike '%Mark Zuckerberg%')
order by surname, name

Here's a sqlfiddle link: http://sqlfiddle.com/#!17/4e6ce/7

user_24557310
Related