Select with null field PostgreSQL

Question

Navigation

#1 by (1 votes)
#2 by (0 votes)

4

I have the following select

SELECT u 
FROM User u 
WHERE lower(u.fullname) 
      || lower(u.username) 
      || lower(u.email)) LIKE %:pesquisa%

It turns out that I want to do the search by any of the 3 existing fields, but the email is not required field.

If I do a search that does not have filled email, it does not return anything.

postgresql

asked by anonymous 18.03.2015 / 15:27

2 answers

score 1 · Answer 1

1

Change to the following:

SELECT u 
FROM User u 
WHERE lower(u.fullname) LIKE '%:pesquisa%'
OR lower(u.username) LIKE '%:pesquisa%'
OR lower(u.email) LIKE '%:pesquisa%'

18.03.2015 / 17:24

score 0 · Answer 2

0

In hibernate there is no COALESCE function, instead you should use the nullif function (column, 'Value if null')

23.11.2015 / 23:57