Handling empty strings in JPQL with an Oracle database

Question

I am trying to write a JPQL query that will work on Oracle, MySQL, or SQL Server. It's supposed to find all the items where a string field is not empty or null. On MySQL,

select x from Examples x where (x.foo != '' and x.foo IS NOT null)

works fine. However, due to the way Oracle converts empty strings to null, the != '' bit always returns false, so I get no results even if hardly any of the strings are empty. I could detect what kind of database the user is using and write a different thing for each, but it's not really nice to have three times as much JPQL. Is there any way of handling empty strings in JPQL that will work for Oracle as well?

I can find lots of related questions, like these:

null vs empty string in Oracle

Why does Oracle 9i treat an empty string as NULL?

https://dba.stackexchange.com/questions/49744/oracle-empty-string-converts-to-null

but nothing really about what to do about it, especially in JPQL. Anyone found a way around this?

Why don't you use a variable and let JPA handle that? Something like .... x.foo != :emptyString ... and query.setParameter("emptyString","")? — Thomas
– Thomas, Commented Jun 29, 2017 at 14:33
That's an interesting idea Thomas, I wouldn't have thought that would make any difference, but now that you mention it, it definitely looks worth a try. Thanks. — Juniper83
– Juniper83, Commented Jun 29, 2017 at 14:51

Kacper · Accepted Answer · 2017-06-29 15:31:40Z

1

This is a bit dirty solution but instead of:

x.foo != ''

you can use:

length(x.foo || ' ') > 1

edited Jun 29, 2017 at 15:31

answered Jun 29, 2017 at 15:24

Kacper

4,8182 gold badges23 silver badges35 bronze badges

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

Collectives™ on Stack Overflow

Handling empty strings in JPQL with an Oracle database

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related