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, @c2 int, @d varbinary(4) | |
while @i <= @n | |
begin | |
set @c = unicode(substring(@s, @i, 1)) | |
if (@c & 0xFC00) = 0xD800 | |
begin | |
set @i += 1 | |
if @i > @n | |
return cast(cast('Malformed UTF-16 - two nchar sequence cut short' as int) as varbinary) | |
set @c2 = unicode(substring(@s, @i, 1)) | |
if (@c2 & 0xFC00) <> 0xDC00 | |
return cast(cast('Malformed UTF-16 - continuation missing in a two nchar sequence' as int) as varbinary) | |
set @c = (((@c & 0x3FF) * 0x400) | (@c2 & 0x3FF)) + 0x10000 | |
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 |
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?
I found this function works correctly :)
https://stackoverflow.com/questions/35200452/compute-md5-hash-of-a-utf8-string/35289890#35289890
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.
Truly a hidden gem. Many thanks for this!
Here's a gist for the converse operation - from a UTF-8 VARBINARY to a NVARCHAR string: https://gist.github.com/sevaa/5a50ba98762762ae960b87fb148a7df2
Works perfectly
Isn't the handling of surrogate pairs incorrect here? According to https://en.wikipedia.org/wiki/UTF-16#Code_points_from_U+010000_to_U+10FFFF , 0x10000 should be added to the combination produced from two surrogate code units, rather than OR'ed with this combination.
Also, this code doesn't check that the second code unit is actually surrogate (or whether it exists at all), so it can produce unexpected/broken results if unpaired surrogate code units are encountered.
I've added some checks for malformed UTF-16 surrogate pairs. The function will now throw a reasonably descriptive error instead of producing garbage outputs.
Since MSSQL 2019, there is native support for UTF-8. The following will do the same job, most likely much faster:
declare @s nvarchar(100) = N'Москва 東京 🙃', @t varchar(200)
set @t = CAST(@s AS varchar) collate Latin1_General_100_CI_AI_SC_UTF8
print @t
Unlike the GitHub comment system, it works correctly with characters from Unicode's astral planes.