SQL 在 DB2 上将 HEX 值转换为 CHAR
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7913300/
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
Convert HEX value to CHAR on DB2
提问by Karsten Str?b?k
In connection with data replication from SQL Serverto DB2I have the following question:
关于从SQL Server到的数据复制，DB2我有以下问题：
On DB2I have a table containing (for simplicity) two columns: COL1and COL2.
在DB2我有一个包含（为简单起见）两列的表：COL1和COL2。
COL1is defined as CHAR(20). COL2is defined as CHAR(10).
COL1被定义为CHAR(20)。COL2被定义为CHAR(10)。
COL1is replicated from SQL by converting a string into hex, e.g. "abcdefghij"to "6162636465666768696A"or "1111111111"to "31313131313131313131"by using the following SQL query:
COL1通过使用以下 SQL 查询将字符串转换为十六进制（例如"abcdefghij"to"6162636465666768696A"或"1111111111"to）从 SQL 复制"31313131313131313131"：
CONVERT(char(20), cast(@InputString as binary) 2)
where @InputStringwould be "abedefghij".
@InputString会在哪里"abedefghij"。
In other words COL1 contains the hex value, but as a string (sorry if the wording is incorrect).
换句话说， COL1 包含十六进制值，但作为字符串（如果措辞不正确，请见谅）。
I need to convert the hex value back to a string and put this value into COL2.
我需要将十六进制值转换回字符串并将此值放入COL2.
What should the SQL query be on DB2to do the convertion? I know how to do this on SQL Server, but not on DB2.
SQL 查询应该在什么上DB2进行转换？我知道如何做到这一点SQL Server，但不知道DB2。
Note: The reason the hex-value is not pre-fixed with "0x"is because style 2 is used in the CONVERTstatement.
注意：十六进制值没有前缀的原因"0x"是因为CONVERT语句中使用了样式 2 。
回答by MrG
select hex('A') from sysibm.sysdummy1;
returns 41. and
返回 41. 和
select x'41' from sysibm.sysdummy1;
gives you 'A'. So you can put that in a forloop and loop through each pair of hex characters to arrive at your original string. Or you can write your own unhex function.
给你'A'。所以你可以把它放在一个for循环中并循环每对十六进制字符来到达你的原始字符串。或者您可以编写自己的 unhex 函数。
Taken from http://www.dbforums.com/db2/1627076-display-hex-columns.html
取自http://www.dbforums.com/db2/1627076-display-hex-columns.html
回答by Eric
DB2 has built-in encoding/decoding.
DB2 具有内置的编码/解码功能。
For OPs question, use....
对于 OP 问题，请使用....
select CAST(ColumnName as char(20) CCSID 37) as ColumnName from TableName where SomeConditionExists
回答by Marcin Rudzki
This is one of most close topics to subject of my problem:
这是与我的问题最接近的主题之一：
I have lost 2 days to figure out how to migrate XML files stored in DB2 BLOB field using SQL Developer. (Yes, migrating to and doing the queries from SQL Developer - we are migrating data to Oracle from DB2, so we were using this tool)!
我花了 2 天时间来弄清楚如何使用 SQL Developer 迁移存储在 DB2 BLOB 字段中的 XML 文件。（是的，迁移到 SQL Developer 并执行查询 - 我们正在将数据从 DB2 迁移到 Oracle，所以我们使用了这个工具）！
How to show XML file/string stored in BLOB?
如何显示存储在 BLOB 中的 XML 文件/字符串？
Let's start with, what the problem was:
让我们开始，问题是什么：
- Data in BLOB was a XML file.
When selected in query, got:
When casted, like:
- BLOB 中的数据是一个 XML 文件。
在查询中选择时，得到：
铸造时，例如：
select CAST(BLOBCOLUMN as VARCHAR(1000)) from TABLE where id = 100;
select CAST(BLOBCOLUMN as VARCHAR(1000)) from TABLE where id = 100;
output was in HEX:
输出为十六进制：
- Nothing worked... Not even the solution from the links in this topic. !NOTHING!
- 没有任何效果......甚至不是本主题中链接的解决方案。！没有！
By mistake found a solution:
错误地找到了解决方案：
CREATE FUNCTION in DB2:
CREATE FUNCTION unhex(in VARCHAR(32000) FOR BIT DATA) RETURNS VARCHAR(32000) LANGUAGE SQL CONTAINS SQL DETERMINISTIC NO EXTERNAL ACTION BEGIN ATOMIC RETURN in; ENDRun SELECT:
select UNHEX( CAST(BLOBCOLUMN as VARCHAR(32000) FOR BIT DATA)) from TABLE where id = 100;Result:
在 DB2 中创建函数：
CREATE FUNCTION unhex(in VARCHAR(32000) FOR BIT DATA) RETURNS VARCHAR(32000) LANGUAGE SQL CONTAINS SQL DETERMINISTIC NO EXTERNAL ACTION BEGIN ATOMIC RETURN in; END运行选择：
select UNHEX( CAST(BLOBCOLUMN as VARCHAR(32000) FOR BIT DATA)) from TABLE where id = 100;结果：
