Skip to content

Instantly share code, notes, and snippets.

@sevaa
Last active Feb 26, 2021
Embed
What would you like to do?
Converting an NVARCHAR string to a UTF-8 VARBINARY data block in pure Transact-SQL
create function [dbo].[ToUTF8](@s nvarchar(max))
returns varbinary(max)
as
begin
declare @i int = 1, @n int = datalength(@s)/2, @r varbinary(max) = 0x, @c int, @d varbinary(4)
while @i <= @n
begin
set @c = unicode(substring(@s, @i, 1))
if (@c & 0xFC00) = 0xD800
begin
set @i += 1
set @c = ((@c & 0x3FF) * 0x400) | 0x10000 | (unicode(substring(@s, @i, 1)) & 0x3FF)
end
if @c < 0x80
set @d = cast(@c as binary(1))
if @c >= 0x80 and @c < 0x800
set @d = cast(((@c * 4) & 0xFF00) | (@c & 0x3F) | 0xC080 as binary(2))
if @c >= 0x800 and @c < 0x10000
set @d = cast(((@c * 0x10) & 0xFF0000) | ((@c * 4) & 0x3F00) | (@c & 0x3F) | 0xe08080 as binary(3))
if @c >= 0x10000
set @d = cast(((@c * 0x40) & 0xFF000000) | ((@c * 0x10) & 0x3F0000) | ((@c * 4) & 0x3F00) | (@c & 0x3F) | 0xf0808080 as binary(4))
set @r += @d
set @i += 1
end
return @r
end
@sevaa

This comment has been minimized.

Copy link
Owner Author

@sevaa sevaa commented Nov 7, 2016

Unlike the GitHub comment system, it works correctly with characters from Unicode's astral planes.

@isken99

This comment has been minimized.

Copy link

@isken99 isken99 commented Oct 24, 2017

Hi Sevaa,
I worked in AP team needs to deal with Asia Characters.
I have tested your SQL function with few Korea and Chinese characters find out the encoding does not meet the internet URL encoding standard.

The left UTF8 is from standard URL encoding, the right one is from the function
〇 E38087 E3C087
ㄱ E384B1 E3C4B1
validated with http://memory.loc.gov/diglib/codetables/9.3.html

There seems to be a pattern of the wrong encoding on the second byte 80 -> C0 and 84 -> C4.

I am not familiar bitwise and hex calculation at all. I really hope to fix it. Will you be quickly discover why?

@isken99

This comment has been minimized.

Copy link

@isken99 isken99 commented Oct 25, 2017

@sevaa

This comment has been minimized.

Copy link
Owner Author

@sevaa sevaa commented Feb 4, 2018

Sorry didn't get to this earlier, only found this comment today. Fixed now, a mask was off in the 3- and 4-byte encoding.

@kmcconnell

This comment has been minimized.

Copy link

@kmcconnell kmcconnell commented Jun 12, 2019

Truly a hidden gem. Many thanks for this!

@sevaa

This comment has been minimized.

Copy link
Owner Author

@sevaa sevaa commented Jul 16, 2019

Here's a gist for the converse operation - from a UTF-8 VARBINARY to a NVARCHAR string: https://gist.github.com/sevaa/5a50ba98762762ae960b87fb148a7df2

@APoint2020

This comment has been minimized.

Copy link

@APoint2020 APoint2020 commented Dec 29, 2020

Works perfectly 👍

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment