在 SQL 中将字符串转换为 HEX
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/219245/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Converting a String to HEX in SQL
提问by Claude Houle
I'm looking for a way to transform a genuine string into it's hexadecimal value in SQL. I'm looking something that is Informix-friendly but I would obviously prefer something database-neutral
我正在寻找一种将真正的字符串转换为 SQL 中的十六进制值的方法。我正在寻找对 Informix 友好的东西，但我显然更喜欢数据库中立的东西
Here is the select I am using now:
这是我现在使用的选择：
SELECT SomeStringColumn from SomeTable
Here is the select I would like to use: SELECT hex( SomeStringColumn ) from SomeTable
这是我想使用的选择：SELECT hex( SomeStringColumn ) from SomeTable
Unfortunately nothing is that simple... Informix gives me that message: Character to numeric conversion error
不幸的是，没有这么简单...... Informix 给了我这样的消息： 字符到数字转换错误
Any idea?
任何的想法？
回答by stephenbayer
Can you use Cast and the fn_varbintohexstr?
您可以使用 Cast 和 fn_varbintohexstr 吗？
SELECT master.dbo.fn_varbintohexstr(CAST(SomeStringColumn AS varbinary))
FROM SomeTable
I'm not sure if you have that function in your database system, it is in MS-SQL.
我不确定您的数据库系统中是否有该功能，它在 MS-SQL 中。
I just tried it in my SQL server MMC on one of my tables:
我只是在我的一张表上的 SQL 服务器 MMC 中尝试过它：
SELECT master.dbo.fn_varbintohexstr(CAST(Addr1 AS VARBINARY)) AS Expr1
FROM Customer
This worked as expected. possibly what I know as master.dbo.fn_varbintohexstr on MS-SQL, might be similar to informix hex() function, so possibly try:
这按预期工作。可能我所知道的 MS-SQL 上的 master.dbo.fn_varbintohexstr 可能类似于 informix hex() 函数，所以可能尝试：
SELECT hex(CAST(Addr1 AS VARBINARY)) AS Expr1
FROM Customer
回答by jhamm
The following works in Sql 2005.
以下在 Sql 2005 中有效。
select convert(varbinary, SomeStringColumn) from SomeTable
回答by Boklucius
what about:
关于什么：
declare @hexstring varchar(max);
set @hexstring = 'E0F0C0';
select cast('' as xml).value('xs:hexBinary( substring(sql:variable("@hexstring"), sql:column("t.pos")) )', 'varbinary(max)')
from (select case substring(@hexstring, 1, 2) when '0x' then 3 else 0 end) as t(pos)
I saw this here: http://blogs.msdn.com/b/sqltips/archive/2008/07/02/converting-from-hex-string-to-varbinary-and-vice-versa.aspx
我在这里看到了：http: //blogs.msdn.com/b/sqltips/archive/2008/07/02/converting-from-hex-string-to-varbinary-and-vice-versa.aspx
Sorrry, that work only on >MS SQL 2005
抱歉，仅适用于 >MS SQL 2005
回答by gusmundo
OLD Post but in my case I also had to remove the 0x part of the hex so I used the below code. (I'm using MS SQL)
旧帖子，但在我的情况下，我还必须删除十六进制的 0x 部分，因此我使用了以下代码。（我正在使用 MS SQL）
convert(varchar, convert(Varbinary(MAX), YOURSTRING),2)
convert(varchar, convert(Varbinary(MAX), YOURSTRING),2)
回答by Josh
If it is possible for you to do this in the database client in code it might be easier.
如果您可以在代码中的数据库客户端中执行此操作，则可能会更容易。
Otherwise the error probably means that the built in hex function can't work with your values as you expect. I would double check the input value is trimmed and in the format first, it might be that simple. Then I would consult the database documentation that describes the hex function and see what its expected input would be and compare that to some of your values and find out what the difference is and how to change your values to match that of the expected input.
否则，该错误可能意味着内置的十六进制函数无法按照您的预期使用您的值。我会仔细检查输入值是否已修剪并首先采用格式，可能就这么简单。然后我会查阅描述十六进制函数的数据库文档，看看它的预期输入是什么，并将其与您的一些值进行比较，找出差异是什么以及如何更改您的值以匹配预期输入的值。
A simple google search for "informix hex function" brought up the first result page with the sentence: "Must be a literal integer or some other expression that returns an integer". If your data type is a string, first convert the string to an integer. It looks like at first glance you do something with the cast function (I am not sure about this).
一个简单的谷歌搜索“informix hex function”会显示第一个结果页面，其中包含以下句子：“必须是文字整数或其他返回整数的表达式”。如果您的数据类型是字符串，请先将字符串转换为整数。乍一看，您似乎使用了 cast 函数（我不确定这一点）。
select hex(cast SomeStringColumn as int)) from SomeTable
