CODEWITHSUNDEEP
sqlpostgresql
Viktor
Viktor

Reputation: 1702

How to use ILIKE in postgres with two columns

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?

Upvotes: 3

Views: 3134

Answers (1)

Gabriel Durac
Gabriel Durac

Reputation: 2760

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

Upvotes: 5

Related Questions