I have a varchar field in a table called REF that is nullable. I want to be able to search on this field using LIKE. eg:
Code:
select b from Bean b where b.ref like :filter
Where "filter" is my input concatenated with '%'. eg: 'abc%'.
The problem is that if REF is null then LIKE will not find it. I could change the query to:
Code:
select b from Bean b where b.ref like :filter
or b.ref IS NULL
but then I will get *all* null results and those starting with 'abc'.
What I need is a conditional where clause. I've tried to use the CASE WHEN expression to handle this but I don't think that it is designed for this task.
For example it would have to do something like this:
Code:
select b from Bean b where b.ref
CASE WHEN :filter IS NULL
THEN like = '%' or b.ref is null
ELSE like concat(:filter, '%')
END
But of course that doesn't make any sense as you can't use the CASE in that location (it wants to be in the place of a value after the 'like').
Is there a workable solution around this or should I rewrite my queries (quite a few nasty ones) using the Criteria API?
Many thanks,
Damian
Login
FAQ
Search
