2

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

Improve this question
4
  • Possible duplicate of Why does Oracle 9i treat an empty string as NULL? Commented Apr 22, 2016 at 14:33
  • 1
    @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. Commented Apr 22, 2016 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.) Commented Apr 22, 2016 at 15:17
  • For example, duplicate of stackoverflow.com/questions/13411819/… and other questions linked to in that thread. Commented Apr 22, 2016 at 15:28

2 Answers 2

Reset to default
5

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>')
Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Oracle treats empty string as NULL - except when it doesn't. Compare, for example, translate('abc', 'a', '') vs. regexp_replace('abc', 'a', '').
@mathguy . . . Or, Oracle accepts NULL string values where you might not expect them.
where name = '' and where name = NULL will return empty set, not fail.
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.

Improve this answer

3 Comments

You an also use WHERE LNNVL(NAME <> INPUT)
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, ...
@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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.