0

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?

Juniper83
  • 35
  • 6
  • Why don't you use a variable and let JPA handle that? Something like `.... x.foo != :emptyString ...` and `query.setParameter("emptyString","")`? – Thomas Jun 29 '17 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 Jun 29 '17 at 14:51
  • I'm afraid it didn't help. Thanks anyway though. – Juniper83 Jun 29 '17 at 21:30

1 Answers1

1

This is a bit dirty solution but instead of:

x.foo != ''

you can use:

length(x.foo || ' ') > 1
Kacper
  • 4,798
  • 2
  • 19
  • 34