UNICODE (Transact-SQL)
Applies to:
SQL Server
Azure SQL Database
Azure SQL Managed Instance
Azure Synapse Analytics
Analytics Platform System (PDW)
SQL analytics endpoint in Microsoft Fabric
Warehouse in Microsoft Fabric
Returns the integer value, as defined by the Unicode standard, for the first character of the input expression.
Transact-SQL syntax conventions
Syntax
UNICODE ( 'ncharacter_expression' )
Note
To view Transact-SQL syntax for SQL Server 2014 (12.x) and earlier versions, see Previous versions documentation.
Arguments
' ncharacter_expression '
Is an nchar or nvarchar expression.
Return Types
int
Remarks
In versions of SQL Server earlier than SQL Server 2012 (11.x) and in Azure SQL Database, the UNICODE function returns a UCS-2 codepoint in the range 000000 through 00FFFF which is capable of representing the 65,535 characters in the Unicode Basic Multilingual Plane (BMP). Starting with SQL Server 2012 (11.x), when using Supplementary Character (SC) enabled collations, UNICODE returns a UTF-16 codepoint in the range 000000 through 10FFFF. For more information on Unicode support in the Database Engine, see Collation and Unicode Support.
Examples
A. Using UNICODE and the NCHAR function
The following example uses the UNICODE
and NCHAR
functions to print the UNICODE value of the first character of the string Åkergatan 24
, and to print the actual first character, Å
.
DECLARE @nstring NCHAR(12);
SET @nstring = N'Åkergatan 24';
SELECT UNICODE(@nstring), NCHAR(UNICODE(@nstring));
Here is the result set.
----------- -
197 Å
B. Using SUBSTRING, UNICODE, and CONVERT
The following example uses the SUBSTRING
, UNICODE
, and CONVERT
functions to print the character number, the Unicode character, and the UNICODE value of each of the characters in the string Åkergatan 24
.
-- The @position variable holds the position of the character currently
-- being processed. The @nstring variable is the Unicode character
-- string to process.
DECLARE @position INT, @nstring NCHAR(12);
-- Initialize the current position variable to the first character in
-- the string.
SET @position = 1;
-- Initialize the character string variable to the string to process.
-- Notice that there is an N before the start of the string, which
-- indicates that the data following the N is Unicode data.
SET @nstring = N'Åkergatan 24';
-- Print the character number of the position of the string you are at,
-- the actual Unicode character you are processing, and the UNICODE
-- value for this particular character.
PRINT 'Character #' + ' ' + 'Unicode Character' + ' ' + 'UNICODE Value';
WHILE @position <= LEN(@nstring)
-- While these are still characters in the character string,
BEGIN;
SELECT @position AS [position],
SUBSTRING(@nstring, @position, 1) AS [character],
UNICODE(SUBSTRING(@nstring, @position, 1)) AS [code_point];
SET @position = @position + 1;
END;
Here is the result set.
Character # Unicode Character UNICODE Value
----------- ----------------- -----------
1 Å 197
----------- ----------------- -----------
2 k 107
----------- ----------------- -----------
3 e 101
----------- ----------------- -----------
4 r 114
----------- ----------------- -----------
5 g 103
----------- ----------------- -----------
6 a 97
----------- ----------------- -----------
7 t 116
----------- ----------------- -----------
8 a 97
----------- ----------------- -----------
9 n 110
----------- ----------------- -----------
10 32
----------- ----------------- -----------
11 2 50
----------- ----------------- -----------
12 4 52
See Also
ASCII (Transact-SQL)
CHAR (Transact-SQL)
NCHAR (Transact-SQL)
String Functions (Transact-SQL)
Collation and Unicode Support