JPA Query: If param is empty string, also get NULL values

Question

I have the following query:

SELECT p FROM dbEntity p where p.name like %:nameFilter%

nameFilter is default an empty String (""), it can also be set to a string the user can enter.

Now the default case gets all rows where the name column is either filled with text or is set to an empty string.
However, there are also rows where name is set to NULL.

How can I change my query to also return the rows with NULL, but only if nameFilter is an empty String?

Ajan Balakumaran · Accepted Answer · 2019-03-06 11:57:38Z

2

You can use coalese and convert nulls as empty strings so that it be retrived,

 SELECT p FROM dbEntity p where coalesce(p.name,"") like %:nameFilter%

edited Mar 6, 2019 at 11:57

answered Mar 6, 2019 at 11:49

Ajan Balakumaran

1,6491 gold badge11 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

JPA Query: If param is empty string, also get NULL values

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related