15

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 !

Bertie
  • 17,277
  • 45
  • 129
  • 182
  • I suppose you could a @PrePersist and check name field for size, replacing it with an " " if it's null. – Erik Apr 01 '11 at 08:08
  • I solve that particular problem by _not_ using a DBMS that's fundamentally flawed. In other words, I use DB2 :-) – paxdiablo Apr 01 '11 at 08:10
  • Some JPA implementations may cater for that by inserting a special character to represent an empty string. This is what we do for you with DataNucleus – DataNucleus Apr 01 '11 at 15:30
  • 1
    From the official Oracle documentation: "Oracle Database currently treats a character value with a length of zero as null. However, this may not continue to be true in future releases, and Oracle recommends that you do not treat empty strings the same as nulls." http://docs.oracle.com/cd/B28359_01/server.111/b28286/sql_elements005.htm This sounds like NULL and "" would always be considered equal - but you (and I) observed that "" is simply stored as NULL, so a comparison with "" may yield a different result, correct? – RobertG Apr 18 '13 at 14:36

6 Answers6

19

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.

Tommi
  • 8,550
  • 5
  • 32
  • 51
  • mi: It wouldnt be an issue for me in the application level. But in the sql level, the statement would be full of checking the nullity when trying to fetch data, because like my example above, if the programmer forgot to add check of is null, then the data wont be retreived. This wouldnt be necessary if Oracle keeps it as an empty string. – Bertie Apr 01 '11 at 08:12
  • But if your column is nullable, isn't that just something you have to live with? If you want to fetch rows `where myfield = 'X'`, and also include the rows where myfield is empty, you just need to add `or myfield is null`. There's no way around that, but I do not really see what's the problem with that. – Tommi Apr 01 '11 at 08:15
  • mi: Let's say in postgresql, the myfield has 'hello', and '', and executing query with where myfield like '%', would show both. But in Oracle, when myfield has 'hello', and '', and executing query with where myfield like '%' will show only one. And '' is really meaningful for us, since it's regarded as a value. – Bertie Apr 01 '11 at 08:27
  • 1
    If you indeed need to separate `''` values from "real" `NULL` values, I'm afraid you have to implement the solution yourself. (Convert empty strings to `" "`, as you said yourself, is a popular option.) Sorry about that. But I would _still_ strongly consider whether this is really needed or not. – Tommi Apr 01 '11 at 08:36
  • 2
    The retrieving language treats them significantly different. A `NULL` String Object (an uninstantiated Object, which is `NULL`) is not the same as an empty String Object (instantiated, with a value of `''`). Code acting on the Object generally expects it to be instantiated; said code would throw a Null Pointer Exception if acting on an unexpectedly uninstantiated Object (the NULL). As a case example, data posted to views might treat a NULL differently than an empty String. Application code backed by a non-Oracle database (MSSQL) would need to add extra null validation on a move to Oracle. – JoshDM May 31 '17 at 16:48
3

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

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 :(

Mike
  • 31
  • 1
2

try

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

select * from mytable where nvl(myfield,-1)=-1;
Dead Programmer
  • 12,427
  • 23
  • 80
  • 112
2

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! ;)

tbone
  • 15,107
  • 3
  • 33
  • 40
  • Sorry for this simple example, but imagine this case like : select * from mytable where myfield1 like '%' and myfield2 = 'xxx'; Any myfield1 whose value is not null will show up. If Oracle were capable of storing empty string however, those value will matched and got shown up. Maybe the question is why i use **like '%'** where i can just skip it. Because at times the filter value could be dynamic based on what the user input, if it's empty, i usually replace it with %, and there goes the **like '%'** If the user inputa 'abc', then it becomes **like '%abc%'** – Bertie Apr 01 '11 at 15:38
  • @Albert, I'm still confused, forgive my thick skull ;) when do you ever need this => and myfield1 like '%' <= if by this you mean where myfield1 is null or myfield1 is not null, then it is meaningless, since all rows will be either null or not null. Also, if you format your query so that you have => where myfield1 like '%abc%' <= you won't use an index anyway, which is why you shouldn't do things this way (imo). But maybe I'm missing something still... – tbone Apr 01 '11 at 18:41
0

Beleive Oracle is optimizing the database by converting the empty string as NULL. First an empty string still may be need to be stored explicitly in the database storage at data block level (*1). Storing as NULL may reduce data storage footprint. Second if any indexes were defined on the column then the NULL values are not included in index thereby reducing index storage footprint. (*2)

From a design and development standpoint unless the empty space really changes the semantics of the data from a business perspective storing as NULL should be fine.

Adding code at framework level ( or parent class ) as suggested above would eliminate the need to type it out at all children classes & objects - that is what Object Oriented and even basic programming strive to do.

If my code is performing best because my database storage is optimized, then I should be happy. It sucks if my code performance tanks in production just because I wanted to save a little typing or failed to do masterful design / development?

I would be happy that Oracle is optimizing it for me under the covers.

All about Oracle NULL:

NULL is a special value. NULL does not equate to anything including itself i.e NULL is not equal to NULL

The World is not Perfect
  • 11
  • "unless the empty space really changes the semantics of the data" : precisely it does! Not often however in almost every application the difference is important for few fields. That's not for nothing that difference exists in most languages. A database should not prevent this distinction. If the Oracle "optimization" was an option that would be great, but it does not offer the choice. And even Oracle knows they're wrong, as @RobertG pointed out https://docs.oracle.com/cd/B28359_01/server.111/b28286/sql_elements005.htm#SQLRF51081 : "this may not continue to be true in future releases" – user1075613 Aug 21 '18 at 18:54