4

Hi today I have met with weird situation. I had a where clause where was condition which returns String and I wanted to check if it's empty or not. And when it returns empty string Oracle still treat it like a different Strings. So I went further and prepared simple queries:

select 1 from dual where 1 = 1;
returns: 1

select 1 from dual where 'A' = 'A';
returns: 1

And now what I cannot understand: 

select 1 from dual where '' = '';
No result.

Even if I check if they are different there is still no result.

select 1 from dual where '' != '';
No result.

Can someone explain it for me ?

Improve this question
3
  • 1
    Empty string are the same as nulls in Oracle. See this question, and the docs, particularly the part about comparisons. Commented Jul 20, 2018 at 12:37
  • 2
    Posisble duplicate? Commented Jul 20, 2018 at 12:49
  • 1
    @AlexPoole Thanks for the links to the documents. Many people have opinions, facts are always welcome. The really curious thing is that, unlike much of Oracle's documentation, this one is clear! Commented Jul 20, 2018 at 16:55

2 Answers 2

Reset to default
7

Oracle treats empty strings as NULL. It's a gotcha. Make a note of it and hope it never bites you in the butt in production.

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

The real "gotcha" is that it's not always treating empty strings as null. 'foo'||'' returns 'foo'. But even more confusing is that 'foo'||null also returns 'foo'
@a_horse_with_no_name - but that is still treating the empty string the same as null? The confusion is that the concatenation operator doesn't return null in both cases as you might expect from how other things behave, I think. (I hadn't noticed before that the docs say that might change one day... is that the same timescale as redefining varchar?)
@AlexPoole: then the concatenation operator is not behaving according to the SQL standard. Because every expression involving null should yield null as in 42 * null
@a_horse_with_no_name I came from Oracle to SQL Server, before Oracle had ANSI syntax. I was hopelessly confused that concatenating a null with a string in SQL Server resulted in null [grin]. Just the opposite of your experience. That was 15 minutes of debugging I wish I could get back :-)
7

The reason is as @Captain Kenpachi explained. If want to compare two strings (or other types that are the same) and want to be tolerant of NULLs (or empty string in Oracle as it treats it as the same) then you need to involve an IS test.

You could try the common cheat of using a rogue value that will never be used but Murphy's Law dictates that one day someone will. This technique also has the drawback that the rogue value should match the type of the thing you are comparing i.e. comparing strings you need a rogue string while comparing dates you need a rouge date. This also means you can't cut-and-paste it liberally without applying a little thought. Example: WHERE NVL(col1,'MyRougeValue')=NVL(col2,'MyRougeValue')

The standard version is to explicitly test for NULLs WHERE (col1=col2 OR (col1 IS NULL AND col2 IS NULL))

The opposite becomes WHERE NOT(col1=col2 OR (col1 IS NULL AND col2 IS NULL))

I have seen the a long winded opposite version (as seen in Toad's data compare tool) WHERE (col1<>col2 OR (col1 IS NULL AND col2 IS NOT NULL) OR (col1 IS NOT NULL AND col2 IS NULL))

Oracle does have a handy DECODE function that is basically is IF a IS b THEN c ELSE d so equality is WHERE DECODE(col1,col2,1,0)=1 and the opposite is WHERE DECODE(col1,col2,1,0)=0. You may find this a little slower than the explicit IS test. It is proprietary to Oracle but helps make up for the empty string problem.

Improve this answer

2 Comments

Rouge or Rogue?
Thanks @William Robertson, I've corrected it. It was only a "cosmetic" issue.

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.