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
- Our original Unicode Text is Åkergatan 24
- 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
- The third character reads ?.
- We passed in abृ, and we expected a Unicode character
Microsoft Sample -03
Outline
- Our original Unicode Text is Åkergatan 24
- We changed the Text to abृ
- 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
- 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.