SQL Server – Transact SQL – Function – Unicode – Online Sample

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 &lt;= 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.

 

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s