Oracle:Difference between NULL and EMPTY string

Question

I am in a situation where my query is not returning any values due to oracle behaviour.

Problem is this:Oracle considers EMPTY STRING as NULL when INSERTING DATA but not when SELECTING BACK the data.

This is not a duplicate of Why does Oracle 9i treat an empty string as NULL? because here i am not asking for the reason to this problem,i am well aware of the reason,i am asking for a solution to this problem.

This is my table structure

   CREATE TABLE TEST
    (
      ID          NUMBER not null,
      NAME VARCHAR2(255)
    )

when inserting the values oracle will accept

INSERT INTO TEST values(1,'');

I found out that internally oracle converts Strings of Zero length to NULL and stores

But my query

SELECT * FROM TEST WHERE NAME = INPUT;(INPUT='')

(Input is passed from front end and will sometimes have empty string)
will not return any result
I can not write dynamic query due to performance issue

Somebody who faced this issue before please let me know how do i compare EMPTY STRING with NULL

Possible duplicate of [Why does Oracle 9i treat an empty string as NULL?](http://stackoverflow.com/questions/203493/why-does-oracle-9i-treat-an-empty-string-as-null) — Vamsi Prabhala, Apr 22 '16 at 14:33
@vkp i am not asking for the reason. i am asking for a solution to this problem.so this is not a duplicate.Before asking this question i already went through that question. — shreesha, Apr 22 '16 at 15:08
Just to second the OP's objection: I don't see how his/her question can possibly be a duplicate of "WHY..." (although I have no doubt it IS a duplicate of something; someone else surely must have asked this before, perhaps it just doesn't show up in a quick search.) — , Apr 22 '16 at 15:17
For example, duplicate of http://stackoverflow.com/questions/13411819/is-there-better-oracle-operator-to-do-null-safe-equality-check and other questions linked to in that thread. — , Apr 22 '16 at 15:28

score 4 · Answer 1 · answered Apr 22 '16 at 14:42

4

The problem is that Oracle (by default) treats empty strings as NULL. Hence:

where name = ''

is the same as:

where name = NULL

and both always fail (because they return NULL).

You can fix this in various ways. One method is:

where (name = INPUT or name is null and INPUT is null)

Or, if you know there is an invalid name:

where coalesce(name, '<invalid>') = coalesce(INPUT, '<invalid>')

answered Apr 22 '16 at 14:42

Gordon Linoff

1,242,037
58
646
786

1

Oracle treats empty string as NULL - except when it doesn't. Compare, for example, translate('abc', 'a', '') vs. regexp_replace('abc', 'a', ''). – Apr 22 '16 at 15:19
@mathguy . . . Or, Oracle accepts `NULL` string values where you might not expect them. – Gordon Linoff Apr 24 '16 at 20:14
`where name = ''` and `where name = NULL` will return empty set, not fail. – Alex78191 Apr 26 '19 at 19:16

score 1 · Accepted Answer · 2016-04-22T14:49:40.613

1

This is one of the most annoying features of Oracle - not found in other DB products. You will have to put up with it, for all the other massive advantages of Oracle - and be prepared that the learning curve is not very quick.

To check for equality of nulls, the best approach is to write explicitly what you are doing, instead of using gimmicks. For example:

... where NAME = INPUT or (NAME IS NULL and INPUT IS NULL)

This will make it a lot easier for yourself, and for others after you, to debug, maintain, and modify the code, now and especially later. There are other solutions, too, but they may confuse others in the future; for example, this is something I wouldn't use (for several reasons):

... where NAME || 'z' = INPUT || 'z'

although it would obviously achieve the same result with less typing.

One more thing, in most cases you should NOT include in your results rows where you treat NULL as "equal" - the values are NULL for a reason, and in most cases if you make two NULL's equal, that is NOT the intended result.

edited Apr 22 '16 at 14:49

answered Apr 22 '16 at 14:43

You an also use `WHERE LNNVL(NAME <> INPUT)` – Wernfried Domscheit Apr 22 '16 at 17:22
1

True... but... this is exactly one of those gimmicks I don't like. In a small organization I may have a C# developer looking at my code, I would surely hope he would understand right away "my way" of writing this. They would certainly have to Google LNNVL, think about why I use <> when I am checking for equality, ... – Apr 22 '16 at 17:26
@WernfriedDomscheit - This thread is more than three years old, but someone just upvoted it so I looked at it again... Note that LNNVL will not work as an alternative; it returns true even if one term is NULL and the other isn't. The correct alternative is to use DECODE, as in `decode(name, input, 1) = 1`. – Aug 05 '19 at 19:47

Oracle:Difference between NULL and EMPTY string

2 Answers2

Linked