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.
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' )
- 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;
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;
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.
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;
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.
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.
You may like to read:
After working for more than 15 years in the Software field, especially in Microsoft technologies, I have decided to share my expert knowledge of SQL Server. Check out all the SQL Server and related database tutorials I have shared here. Most of the readers are from countries like the United States of America, the United Kingdom, New Zealand, Australia, Canada, etc. I am also a Microsoft MVP. Check out more here.