為什麼在 SQL Server 中將 Base64 字元串解碼為 NVARCHAR 時得到不正確的字元？

我一直在研究如何使用 SQL Server 解碼 Base64，並且在網上搜尋了許多解決方案（一些來自這裡）之後似乎是基於這種方法的。

SELECT CAST(CAST('Base64StringHere' as XML ).value('.','varbinary(max)') AS VARCHAR(250))

當我有 ASCII 文本時，這非常有效。但是，當我有以下法語文本時，它會被損壞（可能是由於 VARCHAR 的限制）。

Où est le café le plus proche?
T8O5IGVzdCBsZSBjYWbDqSBsZSBwbHVzIHByb2NoZT8=

並提供以下輸出。

Où est le café le plus proche?

我認為相對簡單的解決方法是將其更改為CAST，NVARCHAR但這會再次導致損壞。

SELECT CAST(CAST('T8O5IGVzdCBsZSBjYWbDqSBsZSBwbHVzIHByb2NoZT8=' as XML ).value('.','varbinary(max)') AS NVARCHAR(250) )

썏₹獥⁴敬挠晡꧃氠⁥汰獵瀠潲档㽥

我的搜尋引擎技能可能讓我失望，但我似乎找不到其他有我問題的人。

有什麼想法嗎？

問題是您將 UTF-8 編碼的字元串編碼為 Base64。因此，解碼 Base64 會返回原始的 UTF-8 字節序列。SQL Server 僅將 UTF-16 Little Endian 用於NVARCHAR數據，甚至用於 XML。因此，是(和 )ù的兩字節 UTF-8 序列的 8 位版本。ù``0xC3``0xB9

幸運的是，可以將 UTF-8 編碼的字元串轉換為 UTF-16，甚至轉換為非 Unicode 程式碼頁（如果程式碼頁支持所有被轉換的字元）。訣竅是將 Base64 解碼字節以文本表示形式（即使轉換錯誤的字元）轉換為 XML。這個技巧的訣竅是您需要添加<?xml ...>聲明（通常省略）並指定源編碼：

DECLARE @Base64Value NVARCHAR(500) = N'T8O5IGVzdCBsZSBjYWbDqSBsZSBwbHVzIHByb2NoZT8=';

DECLARE @BinaryValue VARBINARY(500) =
   CONVERT(XML, @Base64Value).value('.','varbinary(max)');

DECLARE @IntermediaryValue VARCHAR(500) = CONVERT(VARCHAR(500), @BinaryValue);

SELECT @BinaryValue, @IntermediaryValue;
-- 0x4FC3B920657374206C6520636166C3A9206C6520706C75732070726F6368653F
-- Oֳ¹ est le cafֳ© le plus proche?


-- This is to NVARCHAR, which will always work:
SELECT CONVERT(NVARCHAR(500),
   CONVERT(XML, '<?xml version="1.0" encoding="UTF-8"?>' +  @IntermediaryValue)
             );
-- Où est le café le plus proche?


-- This is to VARCHAR, but "success" will depend on the Code Page
-- specified by the default Collation of the current Database:
SELECT CONVERT(VARCHAR(500),
   CONVERT(XML, '<?xml version="1.0" encoding="UTF-8"?>' +  @IntermediaryValue)
             );

-- In a DB with a Latin1_General Collation it works:
-- Où est le café le plus proche?


-- In a DB with a Hebrew Collation, it gets the following error:
/*
  Msg 6355, Level 16, State 1, Line XXXXX
  Conversion of one or more characters from XML to target collation impossible
*/

請注意，此技巧僅適用於從各種源編碼轉換為UTF-16 Little Endian（因為這就是 SQL Server 中的 XML 數據類型在內部儲存字元串的方式）。此方法不能用於將 UTF-16 轉換為 UTF-8 或某些其他非 SQL Server 支持的編碼。

---

下面是一個封裝了上述步驟的 Inline-TVF：

GO
CREATE FUNCTION dbo.ConvertBase64EncodedUTF8ToUTF16LE
(
 @Base64EncodedUTF8String VARCHAR(8000)
)
RETURNS TABLE
AS RETURN

   SELECT 
       CONVERT(NVARCHAR(500),
               CONVERT(XML,
                       '<?xml version="1.0" encoding="UTF-8"?>' +
                       CONVERT(VARCHAR(500),
                               CONVERT(XML, @Base64EncodedUTF8String)
                                 .value('.','varbinary(max)')
                              )
                      )
              ) AS [DecodedValue];

GO

然後測試：

SELECT *
FROM   dbo.ConvertBase64EncodedUTF8ToUTF16LE(
         'T8O5IGVzdCBsZSBjYWbDqSBsZSBwbHVzIHByb2NoZT8=');
-- Où est le café le plus proche?

CROSS APPLY如果進行基於集合的操作，只需使用 with 。

引用自：https://dba.stackexchange.com/questions/212160