Background

Earlier this week I was helping a family member with an exercise.

Transact SQL – Unicode

His exercise involved using the Unicode function.

Sample

Googling landed me at the official Microsoft documentation for the Unicode Function.

Documentation

Unicode Transact SQL
Link

Microsoft Sample

Microsoft Sample -01

Code

-- 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,   
      CONVERT(char(17), SUBSTRING(@nstring, @position, 1)),  
      UNICODE(SUBSTRING(@nstring, @position, 1));  
   SELECT @position = @position + 1;  
   END;  

Output

 

 

Microsoft Sample -02

Outline

1. Our original Unicode Text is Åkergatan 24
2. Let us change to abृ

Code

-- 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';  

-- N'abृ'N'abृ'
SET @nstring = N'abृ';  

-- 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,   
      CONVERT(char(17), SUBSTRING(@nstring, @position, 1)),  
      UNICODE(SUBSTRING(@nstring, @position, 1));  
   SELECT @position = @position + 1;  
   END;  

Output

Explanation

1. The third character reads ?.
2. We passed in abृ, and we expected a Unicode character

 

 

Microsoft Sample -03

Outline

1. Our original Unicode Text is Åkergatan 24
2. We changed the Text to abृ
3. Added a new column for Unicode Character
   • The new column does not have convert ( char(17), … )

Code

set nocount on ;

-- 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';  

-- N'abृ'N'abृ'
SET @nstring = N'abृ';  

-- 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,   
      CONVERT(char(17), SUBSTRING(@nstring, @position, 1)),  
      UNICODE(SUBSTRING(@nstring, @position, 1));  
   */
      SELECT 

              [@position]
                = @position

            , [Unicode Character]
                = CONVERT(char(17), SUBSTRING(@nstring, @position, 1))

            , [Unicode Character - Revised]
                = SUBSTRING(@nstring, @position, 1)

            , [UNICODE Value]
                = UNICODE(SUBSTRING(@nstring, @position, 1))
        ;  	

   SELECT @position = @position + 1;
     
   END;  

Output

Explanation

1. The third column correctly displays our third character

 

Addendum

2020-September-23rd

Microsoft has updated its online documentation for the Unicode Function.

Here is the link.