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 I want to do the search for any of the 3 existing fields, but email no required field.
If I do a survey that has no completed email it returns nothing.
How would you feel about adding one
|| u.email is null
?– Maniero
hello bigown, I followed your suggestion, but what’s catching that I forgot to mention, is the Hibernate, take a look at the stacktrace. Exception Description: Syntax error Parsing [SELECT u FROM User u WHERE Lower(u.fullname) || Lower(u.username) || Lower(u.email) is null LIKE search]. Thanks for the help
– Fernando
Not what I told you to use, I’ll put the full syntax: SELECT u FROM User u WHERE Lower(u.fullname) || Lower(u.username) || Lower(u.email) || u.email is null LIKE %:search%
– Maniero
Thanks again for trying, but Exception persists, I will try other alternatives.
– Fernando
http://www.postgresql.org/docs/8.1/static/functions-conditional.html use COALESCE null string do not concatenate.
– Motta
Motta, already tested with the Coalesce, but the Hibernate does not recognize the function. Thanks
– Fernando