EMPTY_CLOB Function - Empty CLOB Value - Oracle to SQL Server Migration

In Oracle, EMPTY_CLOB() function allows you to assign an empty string (with 0 length) to a CLOB or NCLOB column. Although the length is 0, the value is not NULL.

In SQL Server, you can use '' constant (empty string).

Using EMPTY_CLOB() in Oracle

Let's create a table with a CLOB and NCLOB column in Oracle and insert data:

Oracle:

   -- Use EMPTY_CLOB in DEFAULT clause
   CREATE TABLE countries
   (
       name VARCHAR2(90),
       notes CLOB DEFAULT EMPTY_CLOB(),
       notes_utf NCLOB DEFAULT EMPTY_CLOB() 
    );
 
   -- Insert a row with the default value:
   INSERT INTO countries (name) VALUES ('France');
 
   -- You can also use EMPTY_CLOB() in INSERT
   INSERT INTO countries VALUES ('United States', EMPTY_CLOB(), EMPTY_CLOB());

Note that if you try to insert '' (empty string constant) explicitly, Oracle inserts NULL:

   -- Try to insert '' (NULL will be inserted)
   INSERT INTO countries VALUES ('United Kingdom', '', '');
   -- Try to insert NULL
   INSERT INTO countries VALUES ('Germany', NULL, NULL);

Now if we query the table, we will have the following result:

  -- Retrieve the length of the LOB columns using LENGTH and DBMS_LOB.GETLEGTH functions
  SELECT name, 
              notes, 
              notes_utf,
              LENGTH(notes), 
              DBMS_LOB.GETLENGTH(notes),
              LENGTH(notes_utf)
  FROM countries;

Result:

Using '' Empty String Constant in SQL Server

In SQL Server, you can use '' constant (empty string) instead of EMPTY_CLOB() function. Let's create a table with VARCHAR(max) and NVARCHAR(max) columns in SQL Server:

SQL Server:

   -- Use '' constant in DEFAULT clause
   CREATE TABLE countries
   (
       name VARCHAR(90),
       notes VARCHAR(max) DEFAULT '',
       notes_utf NVARCHAR(max) DEFAULT '' 
    );
 
   -- Insert a row with the default value:
   INSERT INTO countries (name) VALUES ('France');
 
   -- You can also use '' constant in INSERT
   INSERT INTO countries VALUES ('United States', '', '');
   INSERT INTO countries VALUES ('United Kingdom', '', '');
 
   -- Try to insert NULL
   INSERT INTO countries VALUES ('Germany', NULL, NULL);

Now if we query the table, we will have the following result:

  -- Retrieve the length of the LOB columns
  SELECT name, notes, notes_utf, LEN(notes) AS length, LEN(notes_utf) AS length_utf
  FROM countries;

Result:

Note that to get the same results as Oracle, you can replace '' with NULL in INSERT statements for SQL Server.

Resources

SQLines Services and Tools

SQLines offers services and tools to help you migrate Oracle databases and applications to Microsoft SQL Server and SQL Azure. For more information, please Contact Us.

Written by Dmitry Tolpeko, dmtolpeko@sqlines.com - September 2012.

You could leave a comment if you were logged in.