Oracle treating empty string as NULL problem for a Java / JPA programmer

Question

How do you handle this situation where Oracle stores the empty string as a null in the database ?

I would like it to be stored as an empty string as it is not as NULL, since issuing the query would be easier.

Something like this would select the empty string and non-empty string, but not the null values

select * from mytable where myfield like '%';

if i would like to select also the null values (which should be originally empty string), i would have to select like this :

select * from mytable where myfield like '%' or myfield is null;

i would love to skip doing or myfield is null all the time later in my sql statements

The current solution i have in mind is to take care of this in the application level, for example, in the entity, i initialize all my String field default value to a space, for example :

@Entity
public class MyEntity {
  private String name = " ";

  public void setName(String name) {
    if (isEmptyString(name)) {
      name = " ";
    }
  }
  ...

}

Or perhaps, i can make use of a new type still unknown to me from Oracle 11g that can keep empty string as it is without changing it to null value ?

Thank you !

Answer 1

Yup, that's the way Oracle functions. Empty strings are treated as nulls.

You can of course "fix" this on application level - for example by storing " " values as you suggested - but first consider, what exactly is the difference with your "empty string" values compared to NULL values? Why do you need to treat them differently? I used to run into this dilemma, too, but usually found out that there are very few cases where I really need to tell the difference.

Answer 2

No, there is no way to treat empty strings as empty strings. Oracle always treats a string of length zero as a NULL value.

Answer 3

It´s not only the selection with special where condition but also the treating of Java String Objects. If you have a String a="" you can call its length method and get 0. If you have a String a=null you get a nullpointer exception when calling length. So working with an oracle db forces you to always check if your string is null before checking length :(

Answer 4

try

create index idx_myfield on mytable(nvl(myfield,-1));

select * from mytable where nvl(myfield,-1)=-1;

Answer 5

Its early for me, but isn't

select * from mytable where myfield like '%' or myfield is null

the same as

select * from mytable

So, Oracle simplifies your life! ;)