Soo, I need to import a text file which contains a date column that does not always contain a value. Obviously, just doing to_date() will fail when said column is empty. So I tried a case statement to limit to_date() calls to those cases when that column was filled.

Just, I could not get it to work. So I tried to simplify that CASE statement and boiled it down to:

select CASE WHEN '' = '' THEN 'equal' ELSE 'not equal' END FROM dual

The result? Not equal. So I tried

select CASE WHEN '' is null THEN 'equal' ELSE 'not equal' END FROM dual

This results in 'equal'.

WTF? I know that the empty string is null, but why doesn't Oracle convert both empty strings to null, so they are equal? Grmpf.

It does convert both strings to null.

It's a basic fundamental principle that NULL is neither equal to NULL, nor it is not equal to NULL.

Null is an unknown. You can't compare an unknown with another unknown. This is like saying "I'm thinking of a number, you're thinking of a number. Neither of us know what both those numbers are. Now ... tell me if they're the same, or different."

 

Not sure if a work-around is being requested, but...

select CASE WHEN NVL('', '<NULL>') = NVL('', '<NULL>') THEN 'equal' ELSE 'not equal' END FROM dual

should do it...  (last used oracle 5+ years ago...)

RaspenJho:

Not sure if a work-around is being requested, but...

select CASE WHEN NVL('', '<NULL>') = NVL('', '<NULL>') THEN 'equal' ELSE 'not equal' END FROM dual

should do it...  (last used oracle 5+ years ago...)

Unless the file really contains the literal '<NULL>'...

Am I the only one to find this awkward to use and counter-intuitive? If Oracle secretly converts empty strings to null, why doesn't it convert the "=" to "IS" while it's at it?

1) There is no 'secretly' about it. Oracle documentation states it plainly : An empty string evaluates to null.

2) Why should it convert to IS? You should be using IS in the first place. 

Okay, another example: Let's say you have a table with 3 columns, two strings and number. Now you want to display all the numbers, with the added requirement that the number should be negated iff the two string columns match. The string columns are allowed to be empty and if they both are, that counts as a match (note that in most cases you would omit the last sentence from the requirements, because it is commonly agreed upon that two empty strings are equal).

Intuitive approach: SELECT number * (CASE WHEN string1&nbsp;= string2 THEN -1.0 ELSE 1.0 END) FROM table

Using Oracle, this would return incorrect results when string1 and string2 both are empty. You'd have to write something like SELECT number * (CASE WHEN string1&nbsp;= string2 OR (string1 IS NULL AND string2 IS NULL) THEN -1.0 ELSE 1.0 END) FROM table

Three conditions instead of one. Do you get used to this once you have worked with Oracle for a couple of years?

Skurry:

Okay, another example: Let's say you have a table with 3 columns, two strings and number. Now you want to display all the numbers, with the added requirement that the number should be negated iff the two string columns match. The string columns are allowed to be empty and if they both are, that counts as a match (note that in most cases you would omit the last sentence from the requirements, because it is commonly agreed upon that two empty strings are equal).

Intuitive approach: SELECT number * (CASE WHEN string1&nbsp;= string2 THEN -1.0 ELSE 1.0 END) FROM table

Using Oracle, this would return incorrect results when string1 and string2 both are empty. You'd have to write something like SELECT number * (CASE WHEN string1&nbsp;= string2 OR (string1 IS NULL AND string2 IS NULL) THEN -1.0 ELSE 1.0 END) FROM table

Three conditions instead of one. Do you get used to this once you have worked with Oracle for a couple of years?

select number * (case when string1 <> string2 then 1 else -1 end) from table;

should do the trick 

Can you do this?:

SELECT number * ( CASE WHEN string1 = string2 OR string1 IS string2) THEN -1.0 ELSE 1.0 END ) FROM table

RaspenJho:

Can you do this?:

SELECT number * ( CASE WHEN string1 = string2 OR string1 IS string2) THEN -1.0 ELSE 1.0 END ) FROM table

 

at least not in Oracle; the keyword IS requires the keyword NULL

My personal favourite:

x = ''

y = x

SELECT CASE WHEN y = x THEN 'As it should be' ELSE 'Lame, Oracle, lame'

woodle:

My personal favourite:

 

x = ''

y = x

 

SELECT CASE WHEN y = x THEN 'As it should be' ELSE 'Lame, Oracle, lame'

What kind of code is that?, SQL Server?

x := '';
y := x;
SELECT CASE
  WHEN y = x THEN 'woodle is smart'
  WHEN y is null THEN 'woodle doesn''t get it'
  ELSE 'wtf?' END
INTO result
FROM dual;

nerdydeeds:

What kind of code is that?, SQL Server?

x := '';
y := x;
SELECT CASE
  WHEN y = x THEN 'woodle is smart'
  WHEN y is null THEN 'woodle doesn''t get it'
  ELSE 'wtf?' END
INTO result
FROM dual;

That's very pretty, although I think you might have meant

ELSE File_Not_Found END

which is, naturally, what you would want in the highly unusual case where y is not null and not equal to x.

The point, which you apparently missed while you were concentrating on 'i' dotting and 't' crossing, is that if you assign the value of one variable to another they can immediately be not equal to each other.

As an exercise you might try to think about the case where x isn't explicitly set to a zero-length string as in the example but where it might the result of some string manipulations where you started out with a non-zero length string, but somewhere along the way your string was magically transformed into something else.  Now imagine if that could happen in Java or C#.

"Elegant, sturdy and bursting with class."

Skurry:

Soo, I need to import a text file which contains a date column that does not always contain a value. Obviously, just doing to_date() will fail when said column is empty. So I tried a case statement to limit to_date() calls to those cases when that column was filled.

Just, I could not get it to work. So I tried to simplify that CASE statement and boiled it down to:

select CASE WHEN '' = '' THEN 'equal' ELSE 'not equal' END FROM dual

The result? Not equal. So I tried

select CASE WHEN '' is null THEN 'equal' ELSE 'not equal' END FROM dual

This results in 'equal'.

WTF? I know that the empty string is null, but why doesn't Oracle convert both empty strings to null, so they are equal? Grmpf.

Try this,

select decode('','','equal','not equal') FROM dual

It does properly compare two null values