sql-server - 选择十六进制/字符转换

Question

我在 SQL 数据库中有一些数据以下面的格式存储，我想将其转换为可读的字符串:

540045005300540049004E00470031003200330034

我想运行某种 SELECT 语句来返回应为 TESTING1234

的文本

它似乎是十六进制格式，每个字符之间用 00 分隔，所以如果我运行这些语句:

SELECT CHAR(0x54)
SELECT CHAR(0x45)

返回:

T
E

有什么方法可以在一条语句中转换整个字符串吗？

谢谢!

Answer 1

00 指向 2-byte-enocding，表示为 NVARCHAR。试试这个

SELECT CAST(0x540045005300540049004E00470031003200330034 AS NVARCHAR(MAX))

或者直接从 HEX 字符串作为字符串:

SELECT CAST(CONVERT(VARBINARY(MAX),'540045005300540049004E00470031003200330034',2) AS NVARCHAR(MAX));

结果是TESTING1234

更多关于字符串编码的背景

SQL-Server 确切地知道两种类型的字符串:

• 1 字节编码 VARCHAR/CHAR
• 2 字节编码 nVARCHAR/nCHAR

1 字节的字符串是扩展的 ASCII，相关的排序规则提供了一个代码页来映射非纯拉丁字符(它不是 utf-8 作为人们有时会告诉)。

2字节的字符串是UCS-2(几乎和utf-16一样)。

我已经更正了上面的单词 unicode，因为它实际上是不正确的。

SQL-Server 无法本地解释许多编码。

上面的字符串看起来适合NVARCHAR，但在任何情况下都不能保证。

更多关于二进制编码的背景

SQL-Server 知道 BINARY 和 VARBINARY 是真正的 BLOB 类型。在 SELECT 的结果中，它们显示为 HEX 字符串，在脚本中您可以使用 HEX 字符串作为 native 输入。但重要的是要知道，这个 HEX 字符串不是实际值!，只是计算机屏幕上人类可读的表示。

还有一个真正的字符串，看起来像一个十六进制字符串(但不是)。

0x123 != '0x123'

如果您有一个字符串，它是一个十六进制字符串，但以“普通”字符串的形式出现(例如，在 CSV 文件或 XML 等基于文本的容器中)，您必须对其进行转换。

而且，与这个问题并没有真正的关系，只是提一下:有更多基于字符串的二进制表示，如 base64。

一些例子

--start with a normal string 
DECLARE @str VARCHAR(100)='This is a test to explain conversions from string to hex to binary and back';
--see the HEX string (real binary!)
SELECT CAST(@str AS VARBINARY(MAX)) ThisIsTheHexStringOfTheString;

--I copy the binary behind the "=" _wihtout_ quotes
DECLARE @ThisIsTheBinary VARBINARY(MAX)=0x546869732069732061207465737420746F206578706C61696E20636F6E76657273696F6E732066726F6D20737472696E6720746F2068657820746F2062696E61727920616E64206261636B;
--This can be re-casted directly
SELECT CAST(@ThisIsTheBinary AS VARCHAR(MAX)) ThisIsReconvertedBinary;

--there is an undocumented function providing a HEX-string from a binary
DECLARE @aHEXstring VARCHAR(MAX)=sys.fn_varbintohexstr(CAST(@str AS VARBINARY(MAX)));
--This string looks exactly the same as above, but it is a string
SELECT @aHEXstring AS ThisIsStringWhichLooksLikeHEX;

--You can use dynamic SQL
EXEC('SELECT CAST(' + @aHEXstring + ' AS VARCHAR(MAX)) AS CastedViaDynamicSQL');
--or CONVERT's abilities (read the documentation!)
SELECT CAST(CONVERT(VARBINARY(MAX),@aHEXstring,1) AS VARCHAR(MAX)) AS ConvertedViaCONVERT