unicode-transact-sql.md

title	UNICODE (Transact-SQL) | Microsoft Docs
ms.custom
ms.date	03/14/2017
ms.prod	sql-non-specified
ms.reviewer
ms.suite
ms.technology	database-engine
ms.tgt_pltfrm
ms.topic	language-reference
f1_keywords	UNICODE UNICODE_TSQL
dev_langs	TSQL
helpviewer_keywords	first character of input expression [SQL Server] UNICODE function Unicode [SQL Server], UNICODE function
ms.assetid	5e3c40b2-8401-4741-9f2a-bae70eaa4da6
caps.latest.revision	35
author	BYHAM
ms.author	rickbyh
manager	jhubbard

UNICODE (Transact-SQL)

[!INCLUDEtsql-appliesto-ss2008-all_md]

Returns the integer value, as defined by the Unicode standard, for the first character of the input expression.

Transact-SQL Syntax Conventions

Syntax

-- Syntax for SQL Server, Azure SQL Database, Azure SQL Data Warehouse, Parallel Data Warehouse  
  
UNICODE ( 'ncharacter_expression' )  

Arguments

' ncharacter_expression '
Is an nchar or nvarchar expression.

Return Types

int

Remarks

In versions of [!INCLUDEssNoVersion] earlier than [!INCLUDEssSQL11] and in [!INCLUDEssSDSfull], the UNICODE function returns a UCS-2 codepoint in the range 0 through 0xFFFF. In [!INCLUDEssSQL11] and later editions, when using SC collations, UNICODE returns a UTF-16 codepoint in the range 0 through 0x10FFFF.

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 Åkergatan 24-character string, and to print the actual first character, Å.

DECLARE @nstring nchar(12);  
SET @nstring = N'Åkergatan 24';  
SELECT UNICODE(@nstring), NCHAR(UNICODE(@nstring));  

[!INCLUDEssResult]

----------- -   
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 <= DATALENGTH(@nstring)  
-- While these are still characters in the character string,  
   BEGIN;  
   SELECT @position,   
      CONVERT(char(17), SUBSTRING(@nstring, @position, 1)),  
      UNICODE(SUBSTRING(@nstring, @position, 1));  
   SELECT @position = @position + 1;  
   END;  

[!INCLUDEssResult]

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  

Examples: [!INCLUDEssSDWfull] and [!INCLUDEssPDW]

C. 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 Åkergatan 24-character string, and to print the actual first character, Å.

DECLARE @nstring nchar(12);  
SET @nstring = N'Åkergatan 24';  
SELECT UNICODE(@nstring), NCHAR(UNICODE(@nstring));  

[!INCLUDEssResult]

----------- -   
197         Å  

D. 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 <= DATALENGTH(@nstring)  
-- While these are still characters in the character string,  
   BEGIN;  
   SELECT @position,   
      CONVERT(char(17), SUBSTRING(@nstring, @position, 1)),  
      UNICODE(SUBSTRING(@nstring, @position, 1));  
   SELECT @position = @position + 1;  
   END;  

[!INCLUDEssResult]

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

NCHAR (Transact-SQL)
String Functions (Transact-SQL)
Collation and Unicode Support