NCHAR Data Type - SQL Server for Oracle DBAs and Developers

In SQL Server, NCHAR data type stores fixed-length string data in Unicode UCS-2 character set.

SQL Server:

  CREATE TABLE states
  (
      id NCHAR(2),
      name NCHAR(20)
   );
 
   -- Data can be inserted using Unicode and regular constant 
   INSERT INTO states VALUES (N'CA', 'California');

Summary information:

Syntax	NCHAR[(n)]
Parameter	n is the maximum number of characters
Range	1 ⇐ n ⇐ 4000
Default	n is 1 in CREATE TABLE	n is 30 in CAST function
On Insert	Right-padded with blanks to n
On Select	Blanks are retrieved	LEN function excludes trailing blanks
Options	ANSI_PADDING has no effect on NCHAR columns
Size	n * 2 bytes
Synonyms	NATIONAL CHAR and NATIONAL CHARACTER

Last Update: Microsoft SQL Server 2012

In SQL Server, NCHAR is a fixed-length data type and if an inserted value is less than the maximum size, it is padded with blanks:

SQL Server:

   -- Name column is defined as NCHAR(20), so 'Texas' is padded to 20 characters 
   INSERT INTO states VALUES ('TX', 'Texas');

Then when you select NCHAR data, blanks are not trimmed:

   -- Select Name column enclosed in ()
   SELECT '(' + name + ')' FROM states;
   # (California          ) 
   # (Texas               )

Note that LEN function excludes trailing blanks:

   -- Calculate the length of data in Name column excluding trailing blanks
   SELECT LEN(name) FROM states;
   # 10
   # 5

To get the column length including trailing blanks you can use DATALENGTH function. It returns the size in bytes, and since each character takes 2 bytes in Unicode UCS-2 you can divide by 2 to get the length in characters.

   -- Calculate the length of data in Name column including trailing blanks
   SELECT DATALENGTH(name)/2 FROM states;
   # 20
   # 20

You can also use COL_LENGTH function that returns the maximum size of a column in bytes, and since each NCHAR column is always padded to its maximum length, COL_LENGTH also returns the size of data in NCHAR:

   -- Get the maximum size of NCHAR column in bytes
   SELECT COL_LENGTH('states', 'name') FROM states;
   # 40
   # 40

You can use NCHAR function to build a Unicode string using code points. NCHAR function converts a code point to the character represented by this code:

SQL Server:

  -- Build 2 character Unicode string from 2 code points
  INSERT INTO states VALUES (NCHAR(0x3A3) + NCHAR(0x3A9), 'Unknown');
 
  SELECT id FROM states WHERE name = 'Unknown';
  # ΣΩ

• SQL Server for Oracle DBAs and Developers
• Oracle to SQL Server Migration Knowledge Base

Microsoft SQL Server 2012 - Books Online

• NCHAR Data Type

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