SQL NCHAR()

The SQL NCHAR() function returns the Unicode character that corresponds to a given integer code. It is used to convert numeric Unicode values into their character representations.

This function is particularly helpful when working with Unicode data, such as displaying symbols, special characters, or language-specific characters based on their code point values.

The NCHAR() function is available in SQL Server and some other SQL databases that support Unicode.

In this tutorial, we will go through SQL NCHAR() String function, its syntax, and how to use this function in SQL statements for string operations, with the help of well detailed examples.

Syntax of SQL NCHAR() Function

The basic syntax of the SQL NCHAR() function is:

</>
Copy
NCHAR(integer_code);

Each part of this syntax has a specific purpose:

  • integer_code: The Unicode integer value (code point) of the character to return. Valid values are between 0 and 65535.

The NCHAR() function returns the character corresponding to the specified Unicode integer code.

Examples: Using NCHAR() in SQL Queries

We’ll go through examples demonstrating the NCHAR() function in SQL, using different Unicode integer values to display characters.

1. Displaying a Basic Character Using NCHAR()

To return the character for Unicode integer code 65 (the letter “A”):

</>
Copy
SELECT NCHAR(65) AS character_output;

This query returns 'A' as it corresponds to Unicode integer 65.

2. Displaying a Special Symbol Using NCHAR()

To display the symbol for Unicode integer code 36 (the dollar sign “$”):

</>
Copy
SELECT NCHAR(36) AS character_output;

This query returns '$', the character for Unicode integer code 36.

3. Using NCHAR() to Display Non-English Characters

To display a non-English character, such as the Greek letter alpha (α), with Unicode integer code 945:

</>
Copy
SELECT NCHAR(945) AS character_output;

This query returns 'α', the Greek letter alpha, which corresponds to Unicode integer code 945.

4. Using NCHAR() in Data Output Formatting

To use NCHAR() to insert a newline or other special Unicode characters as part of a formatted output:

</>
Copy
SELECT CONCAT('Total: ', NCHAR(36), '100.00') AS formatted_output;

This query returns 'Total: $100.00', using NCHAR(36) to add a dollar sign between text and numeric values in the formatted output.

FAQs for SQL NCHAR()

1. What does the SQL NCHAR() function do?

The NCHAR() function returns the Unicode character for a specified integer code, allowing you to convert numeric codes into character representations.

2. Can NCHAR() handle values outside the Unicode range?

No, NCHAR() accepts integer values only within the Unicode range of 0 to 65535. Values outside this range will cause an error.

3. Is NCHAR() supported in all SQL databases?

NCHAR() is supported in SQL Server and some other databases that handle Unicode data, though support may vary across platforms.

4. Can NCHAR() be used to display emojis?

Yes, NCHAR() can display emojis, provided the Unicode integer code falls within the supported range and the database and display support emojis.

5. Can NCHAR() be combined with other string functions?

Yes, NCHAR() can be combined with other functions like CONCAT() to create formatted strings with special characters.