SQL Server UNICODE Function - SQL Server Guides

In this SQL Server tutorial, I will show you how to handle the character sets using the SQL Server UNICODE function.

suppose you are handling a database based on the global label containing different types of data in different languages. This function is very helpful in that case, allowing you to handle the international character sets.

In other words, it is helpful for developers who work on multilingual databases. I will explain everything about it, from its definition syntax and using the NULL value and table column.

Table of Contents

What is SQL Server UNICODE Function?

Before knowing the SQL Server UNICODE function, let’s know about ‘What is Unicode?’ Unicode is an international character encoding standard with a unique number for every character or symbol.

If you want to know more about UNICODE standards, check out this page about Unicode; now, after knowing Unicode, it becomes easier to understand the UNICODE() function in SQL Server.

So, the UNICODE() function returns the integer value of the beginning character of the given input expression corresponding to the specific characters in the UNICODE standard.

For example, you have the country name ‘United States’. The beginning character or the first character of this string is the letter ‘U’, so if you pass this input expression to the UNICODE() function, it returns the integer value of that first character, such as 85.

Similarly, if you pass any input expression, it returns the corresponding UNICODE integer value of the first character of the given input expression.

The syntax is given below.

UNICODE ( 'ncharacter_exp' )

Where,

UNICODE(): This is the function which accepts the input expression and returns the integer value of the first character in the input expression.
ncharacter_exp: This is the data type of the input expression, and it can be nchar and nvarchar data type.

The return type of the UNICODE() function is an integer type or int. Also, remember that when you pass the NULL value to this function, instead of a valid expression, it returns the NULL.

For example, you have an input expression as ‘USA’, and to know the UNICODE integer of the first character of the input expression, run the query below.

SELECT UNICODE('USA') AS IntegerValue;

The UNICODE() function returns the integer value of 85 for the input expression ‘USA’; this 85 integer is the value of the character ‘U’ in the input expression.

Take one more example, where we will pass the number as an input expression to the UNICODE() function. For example, you have a ranking number as 13, which is the USA’s ranking in education.

Now, use the query below to know the UNICODE integer value for the input expression 13.

SELECT UNICODE(13) AS IntegerValue;

The Unicode integer value for the number 1 in 13 is 49, which is returned by the UNICODE() function that you can see in the output of the above query.

Again, let’s try with special characters such as @, so execute the query below.

SELECT UNICODE('@') AS UnicodeValue;

SQL Server UNICODE Function with Special Characters

The UNICODE value for the special character ‘@’ is 64. Similarly, you can find the integer value for any special characters using the UNICODE() function in SQL Server.

From the above examples, you now understand the workings of the SQL UNICODE function.

SQL Server UNICODE Function with NULL Value

If you pass the NULL value as an input expression to the UNICODE() function, it returns the NULL; for example, execute the query below.

SELECT UNICODE(NULL) AS UnicodeValue;

SQL Server UNICODE Function with NULL Value

From the above output, the UNICODE() function returns the NULL value for the NULL type input expression.

So always include the null value or any other error handler in your database.

Using SQL Server UNICODE Function on Table Column

Here, you will understand the real implementation of the UNICODE() function in SQL Server, where you will generate the code for data exchange by using this function on the table column.

For example, you have the Employees table shown below.

Using SQL Server UNICODE Function on Employees Table Column

As you can see, the table contains three columns EmployeeID, FirstName, and LastName. So, when data is exchanged with external systems, it should have consistent encoding.

Let’s say you export the name of the employees to a system where you need to convert each character of the name to a UNICODE equivalent, which provides seamless data exchange.

For that, use the query below.

SELECT UNICODE(FirstName), UN AS UnicodeValue FROM Employees;

Using SQL Server UNICODE Function on Table Column

Look at the result set. Each name of the FirstName column is converted to its equivalent UNICODE value using the UNICODE() value.

This is just an example of using the SQL Server UNICODE function on the table; you can use it for data validation, sorting, and comparison.

Conclusion

In this SQL Server tutorial, you learned how to find the Unicode value of the first character in a string, number, or special character using the SQL Server UNICODE Function.

Also, you passed the NULL value to the UNICODE() function and saw it return the NULL result. Finally, you have applied the UNICODE() function on the table column to convert the first character of the employee’s name to its Unicode value equivalent.