All my blogs with #SQLSERVERLearning is intended to cover all stuff identified as difference between Oracle and SQL Server.
Hopefully it might be helpful for Oracle guy also working in SQL Server world.
All finding or Observations with SQL Server for any Oracle guy can be access here.
Most of times during our day to day development, we work with Concatenation of string data type in database.
Its important to understand how it works with Empty String ” and NULL while concatenating in Oracle and SQL Server.
Very interesting blog with Quiz on Aggregation and Concat with Empty String and NULL and help me to explore difference between NULL and Empty String between Oracle and SQL Server.
Click to Explore
Lets first start with Oracle and try to understand how it treat NULL and Empty String.
As we can see for Empty String also Oracle project it as NULL.
Oracle Treat Empty String as NULL, So ideally in oracle Empty String and NULL are treated Same.!
With NULL we are aware, NULL with any other expression force output to be NULL.
SELECT NULL + 1 , NULL -1 , NULL *1 , NULL /2 FROM DUAL;
Output for each operator will be NULL.
But it differ on how it is treated with in Concat operations in oracle.With concate in oracle, NULL does not cause output to be NULL.
|| operator will project operand that is not null, concat with NULL or Empty String will not cause make it NULL.
SELECT 'A' || '1' , 'A' || '' , 'A' || NULL , NULL || '' FROM DUAL
'A'||'1' 'A'||'' 'A'||NULL NULL||''
-------- ------- --------- --------
A1 A A NULL
With Concat in oracle, output will be NULL only when all operand is NULL or empty string.
As a best practise for concat with NULL, it is always advisory to use NVL function to replace NULL with Empty String ”.
In SQL Server NULL and Empty String are treated different.
As we can see in output projected, Empty Sring is not displayed as NULL.
It is just Empty.
Observations in SQL Server:
1. Concat with any NULL operand is always NULL and depend on Setting of “CONCAT_NULL_YIEDS_NULL”
2. Concat of all Empty String Operand is Empty String.
2. Concat with Empty String ” is NON Empty String operand.
With SQL Server, we have SET option “CONCAT_NULL_YIEDS_NULL” that influence concat with NULL behavior.
SET CONCAT_NULL_YIELDS_NULL OFF;
SELECT 'SQL' + NULL;
OUTPUT : SQL
So Concat with NULL or Empty String differ a lot in both database.
Hope information share was valuable, please share with others.