Understanding SQL Server NCHAR String() Function With Example

Summary: In this article, you will learn the way to use the SQL Server string function NCHAR data type to store fixed-length, and Unicode character string data.

Definition and Usage

To store fixed-length and Unicode character string data in the database, you need to use the SQL Server NCHAR data type:



In this syntax:

  • Here, n specifies the string length which range is from 1 to 4,000. The storage size of a NCHAR value is two(2) times n bytes.

    The ISO synonyms for NCHAR are NATIONAL CHAR and NATIONAL CHARACTER, therefore, you’ll be able to use them interchangeably.

Note:Important Note: Similar to the CHAR data type, you will use the NCHAR for storing fixed-length character string only. furthermore, If the lengths of data values are variable, then you should consider using the VARCHAR or NVARCHAR data type.

SQL Server NCHAR Example

The given below statement creates a new table with one NCHAR column:

The following given below INSERT statement inserts the character a (щ) in Russian into the NCHAR column:

Notice that you should prefix the Unicode character string constants with the letter N. Otherwise, SQL will convert the string to the default code page of the database, therefore, which can not recognize some certain Unicode characters.

Furthermore, if you insert a character string whose length is greater than the length specified in the column definition, then SQL Server issues an error and terminates the statement.

For demonstration, the following given below statement attempts to insert a string with two(2) characters into the val column of sqlnchar table:

SQL Server shows the following error message:

Furthermore, To find the number of characters and the number of bytes of the values the val column, then you will use the LEN and DATALENGTH functions as following given below:

SQL DateLength output

Difference Between CHAR vs. NCHAR

There are the following major differences between the both data types:

its Store only the non-Unicode characters. Store only Unicode characters in the form of UNICODE UCS-2 characters.
Need one(1) byte to store a character Need two(2) bytes to store a character.
This can Store up to 8000 characters only. This Store up to 4000 charactersonly.
The CHAR data type storage size equals the size specified in the column definition or variable declaration. These data type storage size equals double the size specified in the column definition or variable declaration.