如何将SQL Server存储文本文件的VARBINARY列转换为实际文本字符串？

• SQL Server表中包含VARBINARY类型的Content列，存储值示例：0x500073007900630068006F006C006F0067006900630061006C00200053007400720061007400...
• 该列实际存储HTML文件内容，需要通过SELECT语句将二进制值转换为可直接读取的HTML文本字符串
• 已尝试三种转换方案均失败：
  1. 执行CONVERT(VARCHAR(max), Content, 2) AS ContentConverted，仅返回去掉0x前缀的十六进制字符串
  2. 执行CAST(Content AS VARCHAR(max)) AS ContentConverted，仅返回单个字母P
  3. 执行CAST(CONCAT('<?xml version="1.0" encoding="UTF-8" ?><![CDATA[',Content,']]>') AS XML).value('.','nvarchar(max)')，抛出“非法XML字符”错误

从存储的二进制特征可以判断，内容采用UTF-16 LE编码：每个英文字符对应2个字节，有效ASCII编码后都跟随0x00填充位（比如大写字母P的ASCII编码为0x50，存储为0x5000），这恰好是SQL Server中NVARCHAR类型原生使用的编码格式。三种方案的错误点分别为：

• CONVERT加样式参数2的作用本身就是将二进制转换为不带0x前缀的十六进制字符串，不可能输出可读明文
• 单字节类型VARCHAR解析二进制时，遇到0x00字节会判定为字符串终止符，因此只读取到第一个字符P就截断
• XML转换方案声明编码为UTF-8，和实际存储的UTF-16编码不匹配，加上HTML可能包含XML规范不允许的控制字符，因此触发非法字符报错

直接将二进制值转换为NVARCHAR(MAX)类型即可，编码完全匹配，不需要额外指定转换样式：

SELECT CONVERT(NVARCHAR(MAX), Content) AS ReadableHtml
FROM 你的目标表名

特殊场景处理：如果写入二进制时带了UTF-16 BOM头（字节顺序标记，转换后会在文本开头显示为一个不可见特殊字符），可以用以下语句去掉头部BOM：

SELECT STUFF(CONVERT(NVARCHAR(MAX), Content), 1, 1, '') AS ReadableHtml
FROM 你的目标表名

无BOM的场景不需要做这层处理。

内容的提问来源于stack exchange，提问作者Travis Miller