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).
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:
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.
Oracle 11g R2 SQL Language Reference
Microsoft SQL Server 2012 - Books Online
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.