Skip to content

Instantly share code, notes, and snippets.

Last active Feb 9, 2022
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)
declare @i int = 1, @n int = datalength(@s)/2, @r varbinary(max) = 0x, @c int, @c2 int, @d varbinary(4)
while @i <= @n
set @c = unicode(substring(@s, @i, 1))
if (@c & 0xFC00) = 0xD800
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
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
return @r
Copy link

pvgoran commented Oct 11, 2021

Isn't the handling of surrogate pairs incorrect here? According to , 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.

Copy link

sevaa commented Oct 11, 2021

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.

Copy link

sevaa commented Nov 7, 2021

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

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