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:
Syntax
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.
SQL Server NCHAR Example
The given below statement creates a new table with one NCHAR column:
|
1 2 3 |
CREATE TABLE sqlnchar ( val NCHAR(1) NOT NULL ); |
The following given below INSERT statement inserts the character a (щ) in Russian into the NCHAR column:
|
1 2 3 |
INSERT INTO sqlnchar (val) VALUES (N'щ'); |
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:
|
1 2 3 |
INSERT INTO sqlnchar (val) VALUES (N'щ ф'); |
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:
|
1 2 3 4 5 6 |
SELECT val, len(val) length, DATALENGTH(val) data_length FROM sqlnchar; |
Difference Between CHAR vs. NCHAR
There are the following major differences between the both data types:
| CHAR | NCHAR |
|---|---|
| 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. |
About Author
