#SQLSERVERLearning : String Concatenation for NULL and Empty String in Oracle and SQL Server

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.

ORACLE ::
WITH DATA AS
(
SELECT 'A' AS COL1 FROM DUAL UNION ALL
SELECT NULL AS COL1 FROM DUAL UNION ALL
SELECT '' AS COL1 FROM DUAL
)
SELECT * FROM DATA;

concat_blog

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 ”.

SQL SERVER
In SQL Server NULL and Empty String are treated different.

sql_concat

As we can see in output projected, Empty Sring is not displayed as NULL.
It is just Empty.

Let see how Concat(+) behave in SQL Server, when we have Empty String or NULL as operand in
sql_concat-1jpg

Observations in SQL Server:
1. Concat with any NULL operand is always NULL.
2. Concat of all Empty String Operand is Empty String.
2. Concat with Empty String ” is NON Empty String operand.

So Concat with NULL or Empty String differ a lot in both database.
Hope information share was valuable, please share with others.

About Deepak Mahto

Having 8+ Years of relevant/exciting/fruitful/challenging/learning in Oracle Technology. Currently working as Oracle Performance Consultant in an MNC. Hold Expertise in Performance Engineering, SQL Tuning, Database Tuning, SQL, PLSQL Development/Design Considerations and Oracle Internals. I enjoy reading on Oracle internals, posting some of my finding or observations and playing football.
This entry was posted in Oracle Internal, SQL Server Learning and tagged , , , , , , . Bookmark the permalink.

2 Responses to #SQLSERVERLearning : String Concatenation for NULL and Empty String in Oracle and SQL Server

  1. AR says:

    Nice post. Thank you. Do you know if there is some way to produce NULL (as a result) when concatenating a NULL with a non-empty string in Oracle?

    Liked by 1 person

    • Deepak Mahto says:

      Hey…
      Thanks for liking the post.!!

      We can have our own custom concat to have null when we try to concat with any expression as NULL or Empty String.

      CREATE OR REPLACE FUNCTION CUSTOM_CONCAT
      (EXP1 IN VARCHAR2 , EXP2 IN VARCHAR2)
      RETURN VARCHAR2
      IS
      BEGIN
      RETURN CASE
      WHEN (ASCII(EXP1) + ASCII(EXP2)) IS NULL THEN NULL
      ELSE EXP1 || EXP2 END;
      END;
      /

      Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s