JPQL / SQL query: where like '%' include null values

Question

I have a simple JPQL query. (But this applies also to an sql query..)

FROM DomainObj d where d.field1 like 'TEST%' and d.field2 like '%';

If the DB contains the following row:

1) field1 -> 'TEST'; field2 -> null

the query return nothing!

If the DB contains the following values:

2) filed1 -> 'TEST'; field2 -> ''

the query return the row!

How can I include also null values while searching for like '%' keeping the query as simple as possible (avoiding and/or clause?)

I'm implementing searching funcionality of an entity in the db.. and I also search by many fields at the same time..

Thank you Marco

Answer 1

You can't directly use nulls in equality tests, because null is un-equal to everything, including itself. That's why there's the is null test, e.g:

select null = null -> null
select null <> null -> null
select 1 = null -> null
select 1 <> null -> null
select 1 + null -> null

essentially null is contagious, and will nullify anything it's compared to, or added in to.

So yes, you'll have to do

SELECT ... WHERE somefield LIKE '%...%' or somefield IS NULL