SQL Reference

NCLOB

NCLOB is a data type in SQL that stands for National Character Large Object. It is primarily used to store Unicode or multibyte data, supporting a larger character set than standard text or varchar data types. The storage size can go up to 4 gigabytes and the data is stored in a manner that allows case-sensitive queries.

Example

CREATE TABLE TestTable (
  ID NUMBER,
  Info NCLOB
);

INSERT INTO TestTable (ID, Info)
VALUES (1, TO_NCLOB('Example Text'));

SELECT * FROM TestTable;

Output

  ID    |       INFO
--------|-----------------
   1    |   Example Text

Explanation

The above SQL script creates a table named TestTable with two columns: ID and Info. ID is of data type NUMBER whereas Info is of data type NCLOB (National Character Large OBject), which can hold a large block of text. A row of data is inserted into TestTable with ID as 1 and Info as ‘Example Text’. The SQL SELECT statement retrieves all rows from TestTable. It shows that the Info data is stored and displayed correctly as a large text object.

The requested information is provided below:

Example

CREATE TABLE Sample_Table (Description NCLOB);
INSERT INTO Sample_Table (Description) VALUES (N'Hello, World!');

SELECT * FROM Sample_Table;

Output

Description
-----------
Hello, World!

Explanation

In the SQL Server reference, the NCLOB data type doesn’t exist like in Oracle. Instead, we use NVARCHAR(MAX). The provided example creates a table named Sample_Table with a single NVARCHAR(MAX) column named Description. The N before the string literal is used to denote that the string is in Unicode. The SQL INSERT INTO statement is then used to insert a Unicode string into the Description field. The final SQL SELECT statement retrieves all records from the Sample_Table table.