将UTF-8的varbinary(max)转换成varchar(max)

Question

将UTF-8的varbinary(max)转换成varchar(max)

13

我有一个varbinary(max)列，其中包含压缩的UTF-8编码文本。我想要解压这些数据并在T-SQL中使用SQL Server的UTF-8功能将其转换为varchar(max)。

我正在寻找一种在从varbinary(max)转换为varchar(max)时指定编码的方法。我唯一成功的办法是创建一个表变量，其中包含一个具有UTF-8排序规则的列，并将varbinary数据插入其中。

DECLARE @rv TABLE(
    Res varchar(max) COLLATE Latin1_General_100_CI_AS_SC_UTF8 
)

INSERT INTO @rv
SELECT SUBSTRING(Decompressed, 4, DATALENGTH(Decompressed) - 3) WithoutBOM
FROM
    (SELECT DECOMPRESS(RawResource) AS Decompressed FROM Resource) t

我在想是否有更优雅和高效的方法，不需要插入到表变量中。

更新：

将其简化为一个不涉及字节顺序标记或压缩的简单示例：

我有字符串“Hello”，使用UTF-8编码，没有BOM存储在变量@utf8Binary中。

DECLARE @utf8Binary varbinary(max) = 0x48656C6C6F20F09F988A

现在我尝试将其赋值给不同的基于字符的变量并打印结果：

DECLARE @brokenVarChar varchar(max) = CONVERT(varchar(max), @utf8Binary)
print '@brokenVarChar = ' + @brokenVarChar

DECLARE @brokenNVarChar nvarchar(max) = CONVERT(varchar(max), @utf8Binary)
print '@brokenNVarChar = ' +  @brokenNVarChar 

DECLARE @rv TABLE(
    Res varchar(max) COLLATE Latin1_General_100_CI_AS_SC_UTF8 
)

INSERT INTO @rv
select @utf8Binary

DECLARE @working nvarchar(max)
Select TOP 1 @working = Res from @rv

print '@working = ' + @working

这将产生以下结果：

@brokenVarChar = Hello ðŸ˜Š
@brokenNVarChar = Hello ðŸ˜Š
@working = Hello

我能使用这种间接的方法正确地解码二进制结果，但我想知道是否有更直接（可能更有效）的方法。

- John Stairs

至少一个示例（压缩）值将有助于我们在这里帮助您。 - Thom A

@Larnu，谢谢你的建议。我添加了一个示例脚本。 - John Stairs

1

非常好的问题。根据文档，CAST(@utf8Binary AS varchar(max)) COLLATE Latin1_General_100_CI_AS_SC_UTF8 应该能够工作，但实际上却没有。 - Heinzi

1

我在沙盒环境中得到了一些非常奇怪的结果...看看这个动画GIF，一个函数的行为因为我创建了另一个数据库而改变； - Thom A

然而，SSMS并不复制该行为（动画GIF），因此看起来像是ADS的缺陷。 - Thom A

2

如果有人感兴趣，可以查看Github Issue，了解ADS行为方面的内容。 - Thom A

4个回答

7

我不喜欢这个解决方案，但这是我必须采用的方法（最初我认为它没有起作用，因为ADS中似乎存在一个bug）。一种方法是在UTF8编码的新数据库中创建一个函数，并将值传递给该数据库中的函数。由于该数据库采用UTF8编码，所以默认排序方式会与本地排序方式不同，并且将返回正确的结果：

CREATE DATABASE UTF8 COLLATE Latin1_General_100_CI_AS_SC_UTF8;
GO
USE UTF8;
GO
CREATE OR ALTER FUNCTION dbo.Bin2UTF8 (@utfbinary varbinary(MAX))
RETURNS varchar(MAX) AS
BEGIN
    RETURN CAST(@utfbinary AS varchar(MAX));
END
GO
USE YourDatabase;
GO
SELECT UTF8.dbo.Bin2UTF8(0x48656C6C6F20F09F988A);

然而，这并不特别“美观”。

- Larnu

@Tornhoof 如果你有不同的解决方案，那么你应该将其发布为一个新答案。虽然上面的代码看起来像是一个多行标量函数，但可能会导致性能下降。 - Thom A

是的，它可能会慢一些，所以不需要发布新答案，我认为你的答案仍然是解决问题的最佳方案。 - Tornhoof

我真心希望不是这样，不幸的是，@Tornhoof。需要一个单独的数据库是不太理想的要求。尽管我甚至自己悬赏了这个问题，看看是否有人有更好的想法，但没有人。这确实表明它目前是“正确”的解决方案，但我不喜欢它。我希望在下一个版本的SQL Server中有更好的解决方案。老实说，我应该检查一下Azure反馈中是否有关于“问题”的工单。 - Thom A

嗨@Larnu，最近我偶然发现了一个黑客技巧，即使在较低版本中也可以使用，请看我的回答。 - Shnugo

3

DECLARE @utf8Binary varbinary(max) = 0x48656C6C6F20F09F988A;
DECLARE @brokenNVarChar nvarchar(max) = concat(@utf8Binary, '' COLLATE Latin1_General_100_CI_AS_SC_UTF8);
print '@brokenNVarChar = ' +  @brokenNVarChar;

- lptr

-1

您没有说明数据是如何压缩的或使用了哪种压缩算法。但如果您正在使用 SQL Server 2016 或更高版本中的 COMPRESS 函数，则可以使用 DECOMPRESS 函数，然后将其转换为 VARCHAR(MAX)。 COMPRESS 和 DECOMPRESS 都使用 GZip 压缩算法。此函数将使用 GZIP 算法解压缩输入表达式值。 DECOMPRESS 将返回一个字节数组（VARBINARY(MAX) 类型）。

CAST(DECOMPRESS([这里放置压缩内容]) AS VARCHAR(MAX))

请参见：COMPRESS (Transact-SQL) 和 DECOMPRESS (Transact-SQL)。

- Dave Black

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Shnugo · Accepted Answer

有一个未记录的黑客技巧：

DECLARE @utf8 VARBINARY(MAX)=0x48656C6C6F20F09F988A;

SELECT CAST(CONCAT('<?xml version="1.0" encoding="UTF-8" ?><![CDATA[',@utf8,']]>') AS XML)
       .value('.','nvarchar(max)');

结果

Hello

即使在没有新的UTF8排序规则版本中，这也适用...

更新：将其作为函数调用

这可以很容易地包装成一个标量函数。

CREATE FUNCTION dbo.Convert_UTF8_Binary_To_NVarchar(@utfBinary VARBINARY(MAX))
RETURNS NVARCHAR(MAX)
AS
BEGIN
    RETURN
    (
    SELECT CAST(CONCAT('<?xml version="1.0" encoding="UTF-8" ?><![CDATA[',@utfBinary,']]>') AS XML)
           .value('.','nvarchar(max)')
    );
END
GO

也可以像这样作为内联表值函数

CREATE FUNCTION dbo.Convert_UTF8_Binary_To_NVarchar(@utfBinary VARBINARY(MAX))
RETURNS TABLE
AS
    RETURN
    SELECT CAST(CONCAT('<?xml version="1.0" encoding="UTF-8" ?><![CDATA[',@utfBinary,']]>') AS XML)
           .value('.','nvarchar(max)') AS ConvertedString
GO

此语句可与 FROM 结合使用，或更适用的是使用 APPLY。