Coalesce(null,”) gets null in Oracle and gets ” in SQL Server?

• Oracle version : 12.1.0.2.0
• SQL Server version : 2012

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?

Answer

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.

sqlfiddle

Here is a link talk about this.

Why does Oracle 9i treat an empty string as NULL?