My code:
select COALESCE (null, '')
from dual
in Oracle returns null result, but
select COALESCE (null, '')
in SQL Server returns a '' result.
Why did this result differ?
They are ANSI standards, shouldn't it be the same?
In Oracle document about NULL
Oracle Database treats a character value with a length of zero as null.
Oracle internally changes the empty string to NULL values. Oracle simply won't let insert an empty string.
select null from dual
the same as
select '' from dual
They are all return NULL.
So when you use select COALESCE (null,'') from dual it will translate to select COALESCE (null,null) from dual in Oracle.
Here is a link talk about this.
As per the Oracle docs:
Oracle Database treats a character value with a length of zero as null.
Which is not ANSI standard.
Assigning a zero-length string to a field results in NULL when dealing with a variable-length character type (e.g. VARCHAR or VARCHAR2). However, when a zero-length string is assigned to a fixed-length character type (e.g. CHAR) the result is that the field is blank-padded to full defined length, as required for the CHAR data type. See this AskTom question for further info.
Have you tried the following code in Oracle and SQL Server:
select COALESCE (null,' ') from dual
select COALESCE (null,' ')
it returns not null result in both Oracle and SQL Server
The main issue is how Oracle and SQL Server treat the 2nd expression ''. In Oracle, it is treated as NULL but SQL Server treats it as an empty space.
