sqlsql-serveroracle-database

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

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?

Solution

  • In the Oracle document about NULL

    Oracle Database treats a character value with a length of zero as null.

    Oracle internally changes the empty string to a NULL value. Oracle simply won't insert an empty string.

    select null from dual

    is the same as

    select '' from dual

    They 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 which talk about this.

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