SQL Server NCHAR() Function: Syntax, Examples, and Use Cases

SQL NCHAR() is one of those small, simple, but powerful SQL functions for designing an efficient global database system. Defining and storing Unicode characters properly is one of the key challenges in working with multilingual databases or special symbols. These include accented letters, mathematical symbols, and special characters from languages such as Chinese and Arabic. The go-to solution for this is the NCHAR() function.

NCHAR() in SQL lets you return the Unicode character of an integer based on its numerical code values. It ensures that your database applications can handle text from virtually any language or character set without data loss or misinterpretation. Whether you're developing a global application, processing emoji data, or working with cross-language datasets, understanding how NCHAR() works is key to mastering Unicode in SQL Server.

This guide explains what the SQL Server NCHAR() function is, its syntax, examples, and practical use cases. If you are a database developer, data analyst, or DBA who works extensively with Unicode characters, dbForge Studio for SQL Server provides the most advanced features for managing them seamlessly.

Let's dive in.

What is the NCHAR() function in SQL Server?

The function of NCHAR() in SQL returns the Unicode character based on the number code (matching a specific integer code value). In other words, the function converts an integer into the corresponding character in the Unicode standard, which is a universal character encoding system that supports virtually every writing system in the world.

Let's see an example of how this works for better understanding.

Assume you execute the following command:

SELECT NCHAR(65);

SQL Server returns A because the Unicode value 65 corresponds to this character.

Unlike the regular CHAR() function, which only works with characters from a limited character set, NCHAR() supports Unicode. This compatibility makes it ideal for handling data in multiple languages, including those with non-Latin scripts such as Arabic, Chinese, Japanese, or Cyrillic. If you work with multilingual databases, global applications, and any system that stores or displays user-generated content from different locales, you benefit significantly from this Unicode compatibility.

SQL Server NCHAR() Syntax

The basic syntax of the SQL NCHAR() is as follows:

NCHAR(integer_expression)

Parameters:

• integer_expression: This entails an integer value that represents a valid Unicode code point. Each value corresponds to a specific Unicode character.

Return type

• nchar(1): This function returns a single Unicode character as the result.

Key difference from CHAR()

NCHAR() and CHAR() return single characters based on numeric codes. The main difference is in their character encoding:

• NCHAR() returns a Unicode character (nchar), which supports all global languages and symbol sets.
• CHAR() returns a non-Unicode character (char) limited to the code page of the database or server.

Based on this key difference, it is better to use NCHAR() whenever you're working with international characters, special symbols, or multilingual data, as it guarantees proper storage and display across different locales and systems.

How NCHAR() works in SQL Server

Each Unicode character, whether a Latin letter, Chinese symbol, emoji, or special punctuation mark, has a unique identifier called a code point. The SQL Server NCHAR() function maps an integer value to its corresponding Unicode code point.

In other words, when you provide an integer to NCHAR() in SQL Server, the system looks up that number in the Unicode table and returns the associated character.

Valid Unicode range

Valid Unicode range in SQL Server refers to the accepted integer values for the NCHAR() function. This value ranges from 0 to 65,535 and represents Unicode code points within the Basic Multilingual Plane (BMP).

BMP is the first and most used section of the Unicode standard, containing many of the world's writing systems, symbols, and characters used in everyday text. It includes all code points from U+0000 to U+FFFF, which is a total of 65,535 possible characters.

Internally, SQL Server stores Unicode data using UTF-16 encoding, which can represent characters outside the BMP (values above 65,535) by combining two 16-bit values known as surrogate pairs.

However, the NCHAR() function itself is limited to the BMP range (0–65,535). This means it can return the most common characters and symbols, but not those that require surrogate pairs, such as certain emojis or historic scripts.

Behavior for invalid values

Since the SQL Server NCHAR() function only works with values from 0 to 65,535, if you supply an integer outside this valid range, SQL Server will return NULL or raise an error, depending on the version and context.

For example, using a value like NCHAR(70000), which exceeds the valid range, will result in an invalid character or NULL output.

CHAR() vs NCHAR() comparison

The primary difference between CHAR() and NCHAR() is in how they interpret numeric values. Have a look at the table below that explains these differences.

Example

SELECT CHAR(200), NCHAR(200);

Result:

• CHAR(200) might return a special symbol (depending on the code page).
• NCHAR(200) consistently returns the Unicode character È, regardless of the system locale.

CHAR() is code-page dependent; NCHAR() is universal, therefore, it is the best choice for multilingual applications.

SQL Server NCHAR() examples

Let's consider the work of the NCHAR() function in SQL Server using practical examples.

Basic example

This example shows how to use the NCHAR() function to convert an integer value into its corresponding Unicode character.

SELECT NCHAR(65) AS Result;

The Unicode value 65 corresponds to the capital letter A. Therefore, the output will be A.

Using NCHAR() with Unicode characters

Beyond converting an integer into its corresponding Unicode character, NCHAR() supports a wide range of Unicode symbols, including currency signs, mathematical symbols, and letters from non-Latin alphabets. Have a look at the example below.

SELECT
  NCHAR(8364) AS EuroSign,
  NCHAR(169) AS CopyrightSign;

8364 is the Unicode code point for the euro symbol (€), while 169 corresponds to the copyright symbol (©). The output is:

Concatenating strings with NCHAR()

You can combine NCHAR() with regular text to build strings that include special or localized characters as shown below:

SELECT 'Price: ' + NCHAR(36) + '100' AS ProductPrice;

The Unicode value 36 represents the dollar sign ($). The output is:

Price: $100

You can replace it with any other symbol, such as NCHAR(8377) for the Indian rupee (₹).

Creating line breaks or tabs with NCHAR()

NCHAR() is also useful for formatting text output, such as adding new lines or tabs in query results. Here is an example:

SELECT
  'Product:' + NCHAR(9) + 'Laptop'
  + NCHAR(13) + NCHAR(10)
  + 'Price:' + NCHAR(9) + '$1200' AS FormattedOutput;

In this query, NCHAR(9) represents a tab character, while NCHAR(13) (carriage return) and NCHAR(10) (line feed) together create a new line in SQL Server. The output will look as follows:

Product: Laptop
Price: $1200

These examples demonstrate the flexibility and power of NCHAR() for displaying, formatting, and managing Unicode characters in SQL Server.

SQL Server NCHAR vs CHAR vs NVARCHAR vs VARCHAR

CHAR, NCHAR, VARCHAR, and NVARCHAR in SQL Server are the four primary character data types. However, they differ in terms of storage, encoding, and ideal use cases. The table below compares these data types and explains the differences.

Key takeaways

• Use NCHAR or NVARCHAR when working with Unicode or multilingual data to ensure compatibility
• Choose CHAR or VARCHAR for non-Unicode (ASCII) text when storage efficiency and legacy compatibility matter
• Refer to NVARCHAR(MAX) for storing large Unicode text, such as documents or internationalized content

For most modern applications that handle global data, NVARCHAR is the safest and most reliable choice.

Conclusion

The SQL Server NCHAR() function plays a crucial role ensuring that your databases can handle the full range of Unicode characters, from accented Latin letters and mathematical symbols to complex scripts such as Chinese, Arabic, or Cyrillic. By mapping integer codes to their corresponding Unicode characters, NCHAR() allows you to store, display, and manipulate multilingual text with complete accuracy.

For even greater efficiency, precision, and comfort in handling Unicode data, try dbForge Studio for SQL Server. Its advanced Unicode handling, intelligent SQL editor, and robust data management tools make it the perfect companion for modern SQL Server development.

FAQ

SELECT 
    'Name:' + NCHAR(9) + 'Alice' 
    + NCHAR(13) + NCHAR(10)
    + 'Country:' + NCHAR(9) + 'Japan';