有没有一个简单的方法在 T-SQL 中将 utf-8 编码的 varbinary(max) 列转换为 varchar(max)？类似于 CONVERT(varchar(max), [MyDataColumn])。最好不需要自定义函数。
目前我在客户端上转换数据，但这样做的缺点是正确的过滤和排序不如在服务器端完成的那么高效。
将UTF-8编码的varbinary(max)数据转换为nvarchar(max)字符串。
11
- Stefan Schönbächler
9
3个回答
10
XML技巧
以下解决方案适用于任何编码。
有一个巧妙的方法可以确切地做到OP所要求的。编辑：我在SO上发现了相同的方法（SQL - UTF-8 to varchar/nvarchar Encoding issue）
该过程如下：
SELECT
CAST(
'<?xml version=''1.0'' encoding=''utf-8''?><![CDATA[' --start CDATA
+ REPLACE(
LB.LongBinary,
']]>', --we need only to escape ]]>, which ends CDATA section
']]]]><![CDATA[>' --we simply split it into two CDATA sections
) + ']]>' AS XML --finish CDATA
).value('.', 'nvarchar(max)')
为什么这行代码有效：varbinary和varchar是相同的二进制位串，只有解释不同，因此生成的xml实际上是utf8编码比特流，并且xml解析器能够重新构造正确的utf8编码字符。
注意使用“nvarchar(max)”在“value”函数中。如果使用“varchar”，它会破坏多字节字符（取决于您的排序规则）。
警惕二：XML不能处理某些字符，例如0x2。当字符串包含这些字符时，此技巧将失败。
数据库技巧（SQL Server 2019及更高版本）
这很简单。创建另一个默认排序规则为UTF8的数据库。创建将VARBINARY转换为VARCHAR的函数。返回的VARCHAR将具有该数据库的UTF8排序规则。
插入技巧（SQL Server 2019及更高版本）
这是另一个简单的技巧。创建一个带有一个列VARCHAR COLLATE ...UTF8的表。将VARBINARY数据插入到此表中。它将正确保存为UTF8 VARCHAR。遗憾的是，内存优化表无法使用UTF8排序规则...
我试图想出一种使用SQL Server 2019的Utf8排序规则的方法，迄今为止我找到了一种可能的方法，应该比XML技巧快（请参见下文）。
1.创建具有VARBINARY列的临时表。 2.将VARBINARY值插入到表中 3.修改表改变为带有utf8排序规则的varchar列。
drop table if exists
#bin,
#utf8;
create table #utf8 (UTF8 VARCHAR(MAX) COLLATE Czech_100_CI_AI_SC_UTF8);
create table #bin (BIN VARBINARY(MAX));
insert into #utf8 (UTF8) values ('Žluťoučký kůň říčně pěl ďábelské ódy za svitu měsíce.');
insert into #bin (BIN) select CAST(UTF8 AS varbinary(max)) from #utf8;
select * from #utf8; --here you can see the utf8 string is stored correctly and that
select BIN, CAST(BIN AS VARCHAR(MAX)) from #bin; --utf8 binary is converted into gibberish
alter table #bin alter column BIN varchar(max) collate Czech_100_CI_AI_SC_UTF8;
select * from #bin; --voialá, correctly converted varchar
alter table #bin alter column BIN nvarchar(max);
select * from #bin; --finally, correctly converted nvarchar
速度差异
- 数据库技巧 和 插入技巧 是最快的。
- XML 技巧 较慢。
- 修改表技巧 很愚蠢，不要使用。当你一次性更改许多短文本时（修改后的表格很大），它会输掉比赛。
测试：
- 第一个字符串包含对 XML 技巧 替换的一个操作。
- 第二个字符串是纯 ASCII ，没有针对 XML 技巧的替换。
@TextLengthMultiplier确定转换文本的长度。@TextAmount确定一次将有多少个文本进行转换。
------------------
--TEST SETUP
--DECLARE @LongText NVARCHAR(MAX) = N'český jazyk, Tiếng Việt, русский язык, 漢語, ]]>';
--DECLARE @LongText NVARCHAR(MAX) = N'JUST ASCII, for LOLZ------------------------------------------------------';
DECLARE
@TextLengthMultiplier INTEGER = 100000,
@TextAmount INTEGER = 10;
---------------------
-- TECHNICALITIES
DECLARE
@StartCDATA DATETIME2(7), @EndCDATA DATETIME2(7),
@StartTable DATETIME2(7), @EndTable DATETIME2(7),
@StartDB DATETIME2(7), @EndDB DATETIME2(7),
@StartInsert DATETIME2(7), @EndInsert DATETIME2(7);
drop table if exists
#longTexts,
#longBinaries,
#CDATAConverts,
#DBConverts,
#INsertConverts;
CREATE TABLE #longTexts (LongText VARCHAR (MAX) COLLATE Czech_100_CI_AI_SC_UTF8 NOT NULL);
CREATE TABLE #longBinaries (LongBinary VARBINARY(MAX) NOT NULL);
CREATE TABLE #CDATAConverts (LongText VARCHAR (MAX) COLLATE Czech_100_CI_AI_SC_UTF8 NOT NULL);
CREATE TABLE #DBConverts (LongText VARCHAR (MAX) COLLATE Czech_100_CI_AI_SC_UTF8 NOT NULL);
CREATE TABLE #InsertConverts (LongText VARCHAR (MAX) COLLATE Czech_100_CI_AI_SC_UTF8 NOT NULL);
insert into #longTexts --make the long text longer
(LongText)
select
REPLICATE(@LongText, @TextLengthMultiplier)
from
TESTES.dbo.Numbers --use while if you don't have number table
WHERE
Number BETWEEN 1 AND @TextAmount; --make more of them
insert into #longBinaries (LongBinary) select CAST(LongText AS varbinary(max)) from #longTexts;
--sanity check...
SELECT TOP(1) * FROM #longTexts;
------------------------------
--MEASURE CDATA--
SET @StartCDATA = SYSDATETIME();
INSERT INTO #CDATAConverts
(
LongText
)
SELECT
CAST(
'<?xml version=''1.0'' encoding=''utf-8''?><![CDATA['
+ REPLACE(
LB.LongBinary,
']]>',
']]]]><![CDATA[>'
) + ']]>' AS XML
).value('.', 'Nvarchar(max)')
FROM
#longBinaries AS LB;
SET @EndCDATA = SYSDATETIME();
--------------------------------------------
--MEASURE ALTER TABLE--
SET @StartTable = SYSDATETIME();
DROP TABLE IF EXISTS #AlterConverts;
CREATE TABLE #AlterConverts (UTF8 VARBINARY(MAX));
INSERT INTO #AlterConverts
(
UTF8
)
SELECT
LB.LongBinary
FROM
#longBinaries AS LB;
ALTER TABLE #AlterConverts ALTER COLUMN UTF8 VARCHAR(MAX) COLLATE Czech_100_CI_AI_SC_UTF8;
--ALTER TABLE #AlterConverts ALTER COLUMN UTF8 NVARCHAR(MAX);
SET @EndTable = SYSDATETIME();
--------------------------------------------
--MEASURE DB--
SET @StartDB = SYSDATETIME();
INSERT INTO #DBConverts
(
LongText
)
SELECT
FUNCTIONS_ONLY.dbo.VarBinaryToUTF8(LB.LongBinary)
FROM
#longBinaries AS LB;
SET @EndDB = SYSDATETIME();
--------------------------------------------
--MEASURE Insert--
SET @StartInsert = SYSDATETIME();
INSERT INTO #INsertConverts
(
LongText
)
SELECT
LB.LongBinary
FROM
#longBinaries AS LB;
SET @EndInsert = SYSDATETIME();
--------------------------------------------
-- RESULTS
SELECT
DATEDIFF(MILLISECOND, @StartCDATA, @EndCDATA) AS CDATA_MS,
DATEDIFF(MILLISECOND, @StartTable, @EndTable) AS ALTER_MS,
DATEDIFF(MILLISECOND, @StartDB, @EndDB) AS DB_MS,
DATEDIFF(MILLISECOND, @StartInsert, @EndInsert) AS Insert_MS;
SELECT TOP(1) '#CDATAConverts ', * FROM #CDATAConverts ;
SELECT TOP(1) '#DBConverts ', * FROM #DBConverts ;
SELECT TOP(1) '#INsertConverts', * FROM #INsertConverts;
SELECT TOP(1) '#AlterConverts ', * FROM #AlterConverts ;
- andowero
5
所以，我挖掘了更多的信息，并提出了3种解决方案。我甚至加速了著名的XML技巧。
我的建议是：使用CLR函数... - andowero
好的想法，但似乎存在无效字符的问题。我收到了消息9420-在位置...处有无效的XML字符。 - Herbert
@Herbert：你能提供一个例子吗？试着找到那个有问题的字符。你尝试过插入技巧或数据库技巧吗？ - andowero
我刚刚使用了上面代码示例中的“本地”XML技巧。在这种情况下，它是作为图像存储的utf-8编码文本。像这样以标准方式转换：CONVERT(varchar(5000),CAST([Text_ASCII_7] AS varbinary(max)),0) AS Titel_Text可以按预期工作：它提供了utf-8编码的字符字符串，ascii格式，所有单个字节均为一个字符。我无法评估无效字符，因为我必须处理大量不断变化的字符串。 - Herbert
如果您的文档中包含XML非法字符，则需要修改XML技巧。根据此答案，在XML中有多个字符不能出现。如果您无法创建新数据库，则建议使用“插入”技巧，如果可以，则使用“数据库”技巧。 - andowero
7
SQL-Server不支持UTF-8（至少在所有可用的版本中都是如此）。从磁盘读取
需要知道的重要信息：
utf-8编码文件时，通过BCP有有限的支持，从v2014 SP2开始（以及支持的版本的一些详细信息）。需要知道的重要信息：
VARCHAR(x)不是utf-8。它是1字节编码的扩展ASCII字符，使用一个代码页（存在于排序规则中）作为字符映射。
NVARCHAR(x)不是utf-16（但非常接近，是ucs-2）。这是一个2字节编码的字符串，涵盖几乎所有已知的字符（但存在例外情况）。
utf-8 将使用1个字节来表示普通拉丁字符，但对于编码的外来字符集，可能需要2个或更多字节。
VARBINARY(x) 将作为无意义的字节串来保存 utf-8。
简单的 CAST 或 CONVERT 是行不通的： VARCHAR 会将每个单独的字节视为一个字符。这显然不是您期望的结果。 NVARCHAR 会将每个2个字节的块视为一个字符。这也不是您需要的东西。
您可以尝试将其写入文件，并使用 BCP（v2014 SP2或更高版本）读取。但我认为更好的选择是使用 CLR函数。
- Shnugo
3
@PanagiotisKanavos，是的...我已经偶然发现了这个问题。但我认为，至少默认情况下几乎没有人使用这些
_SC 排序规则。更改数据库的排序规则是一项非常困难的操作。将这些排序规则写入您需要的每个字符串也是一个非常丑陋和耗时的过程...此外，我认为文档并不绝对精确，因为它将 NVARCHAR 称为 unicode / utf-16 等效物。嗯，在99.9%的情况下是这样，但并不是任何UTF-16字符串都可以通过转换为 VARBINARY 再转换回 NVARCHAR 来工作... - Shnugo可能是因为我们没有遇到任何问题，即使涉及表情符号。它们可能不在UCS-2中，但正如文档所解释的那样，它们不会被破坏。 - Panagiotis Kanavos
0
您可以使用以下内容将字符串发布到 varbinary 字段中
Encoding.Unicode.GetBytes(Item.VALUE)
然后使用以下代码将数据作为字符串检索
public string ReadCString(byte[] cString)
{
var nullIndex = Array.IndexOf(cString, (byte)0);
nullIndex = (nullIndex == -1) ? cString.Length : nullIndex;
return System.Text.Encoding.Unicode.GetString(cString);
}
- Hazem AbdelHamid
网页内容由stack overflow 提供, 点击上面的可以查看英文原文，
原文链接
原文链接
- 相关问题
- 3 将NTEXT和nvarchar(255)转换为nvarchar(max)的问题
- 3 在Flutter中将图像文件转换为VARBINARY(max)
- 10 SQL Server，将NTEXT转换为NVARCHAR（MAX）
- 13 在SQL Server中将base64字符串转换为varbinary(max)
- 5 将nvarchar(max)转换为varbinary(max)
- 3 使用VARCHAR（MAX），NVARCHAR（MAX）和VARBINARY（MAX）代替TEXT，NTEXT和IMAGE有哪些好处？
- 4 如何在T-SQL中将带有filestream的varbinary(max)转换为实际的varbinary(max)？
- 7 将nvarchar（4000）转换为nvarchar（max）
- 3 如何从NVARCHAR(MAX)属性中解析UTF-8编码的XML？
- 13 将UTF-8的varbinary(max)转换成varchar(max)
nvarchar(...)，对于CLOBs使用nvarchar(max)。不需要任何函数或转换，也没有代码页转换错误的机会。您的客户端代码库或驱动程序将能够轻松存储Unicode文本，无需复杂操作。 - Panagiotis Kanavosnvarchar的数据库吗？还是只是假设Unicode意味着UTF8？ - Panagiotis Kanavosnvarchar字段，并通过触发器更新它以保存UTF16文本。这将允许您应用JSON函数、对其进行索引、指定排序规则等操作。您可以在表上使用透明压缩（自2016SP1以来即可在SQL Server Express中使用），以减少空间占用并通过减少IO来提高性能。 - Panagiotis Kanavos