SQL UNICODE function is used for returning UNICODE value, i.e., an integer value for the first character of expression. The SQL UNICODE is one of the SQL String Function, which is used to return an integer value, as defined in Unicode standards.
If we specify the character string (more than one character), then a UNICODE function will return an integer value for the leftmost character of a character expression.
SQL UNICODE Function
One of the functions included in the T-SQL is the UNICODE() function.
You can use the UNICODE function with the SQL Server (and Azure) to return the Unicode value of a given character.
This function works similar to an ASCII() function, except that it returns a Unicode value.
The UNICODE() function returns the integer value (the Unicode value), for the first character of the input expression.
Syntax
SELECT UNICODE (Expression);
Parameters
Expression: An expression whose first character Unicode value has to be returned. It can be a nchar or varchar expression.
Example
Query 1
See the following query.
SELECT UNICODE (‘AppDividend.com’);
Output
65
Query 2
See the following query.
SELECT UNICODE (‘123SQL123’);
Output
49
For checking Unicode value for every character of expression, then the following query has to be written in PL/SQL oracle.
Query 3
See the following query.
DECLARE @i INT, @str NCHAR(11); SET @i=1; SET @str= ‘APPDIVIDEND’; While @i <= LEN(@str) BEGIN SELECT SUBSTRING (@str, @i, 1) AS [Nchar_Value], UNICODE (SUBSTRING (@str, @i, 1)) AS [UNICODE_VALUE] SET @i=@i+1; END;
See the output.
Nchar_Value | UNICODE_VALUE |
A | 65 |
P | 80 |
P | 80 |
D | 68 |
I | 73 |
V | 86 |
I | 73 |
D | 68 |
E | 69 |
N | 78 |
D | 68 |
Let’s apply the UNICODE function to a table.
Table: Employee
Emp_id | Emp_name | City | State | Salary |
101 | Rohit | Patna | Bihar | 30000 |
201 | Shivam | Jalandhar | Punjab | 20000 |
301 | Karan | Allahabad | Uttar Pradesh | 40000 |
401 | Suraj | Kolkata | West Bengal | 60000 |
501 | Akash | Vizag | Andhra Pradesh | 70000 |
Suppose we want to print the Unicode value of Emp_name from the Employee table. Then the following query has to be written.
Query 4
Select Emp_name, UNICODE (Emp_name) AS UNICODE_VALUE from EMPLOYEE;
Output
Emp_name | UNICODE_VALUE |
Rohit | 82 |
Shivam | 83 |
Karan | 75 |
Suraj | 83 |
Akash | 65 |
Here, you can see that UNICODE_VALUE of every Employee name is displayed using the above query.
Unicode vs. ASCII
SQL UNICODE() function works similar to the ASCII() function, but with the exception that it returns the Unicode value as opposed to the ASCII value.
Note that, for the first 128 characters, the ASCII and Unicode values are the same, and therefore, these two functions will produce the same results for the first 128 characters.
However, the UNICODE() function will work with the much larger range of characters due to the 128 character limitation of an ASCII standard.
Here’s the example of the ASCII() and UNICODE() functions side by side.
See the following query.
SELECT UNICODE('A') AS Unicode, ASCII('A') AS ASCII;
See the output.
Unicode | ASCII | |
---|---|---|
1 | 65 | 65 |
So we can see that they returned the same result in this case. This is due to the fact that the letter A falls within the range of ASCII codes (and therefore is also included within the Unicode range).
Finally, SQL UNICODE Function Example | SQL Server UNICODE() Tutorial is over.