在 DB2 上将 HEX 值转换为 CHAR [英] Convert HEX value to CHAR on DB2

问题描述

关于从 SQL Server 到 DB2 的数据复制，我有以下问题:

In connection with data replication from SQL Server to DB2 I have the following question:

在 DB2 上，我有一个包含(为简单起见)两列的表:COL1 和 COL2.

On DB2 I have a table containing (for simplicity) two columns: COL1 and COL2.

COL1 定义为 CHAR(20).COL2 定义为 CHAR(10).

COL1 is defined as CHAR(20). COL2 is defined as CHAR(10).

COL1 通过将字符串转换为十六进制来从 SQL 复制，例如"abcdefghij" 到 "6162636465666768696A" 或 "1111111111" 到 "31313131313131313131" 使用以下 SQL 查询:

COL1 is replicated from SQL by converting a string into hex, e.g. "abcdefghij" to "6162636465666768696A" or "1111111111" to "31313131313131313131" by using the following SQL query:

CONVERT(char(20), cast(@InputString as binary) 2)

其中 @InputString 将是 "abedefghij".

换句话说，COL1 包含十六进制值，但作为字符串(如果措辞不正确，请见谅).

In other words COL1 contains the hex value, but as a string (sorry if the wording is incorrect).

我需要将十六进制值转换回字符串并将这个值放入 COL2.

I need to convert the hex value back to a string and put this value into COL2.

DB2 上的 SQL 查询应该是什么来进行转换?我知道如何在 SQL Server 上执行此操作，但在 DB2 上不知道.

What should the SQL query be on DB2 to do the convertion? I know how to do this on SQL Server, but not on DB2.

注意:十六进制值不以 "0x" 为前缀的原因是 CONVERT 语句中使用了样式 2.

Note: The reason the hex-value is not pre-fixed with "0x" is because style 2 is used in the CONVERT statement.

推荐答案

select hex('A') from sysibm.sysdummy1; 

返回 41.和

select x'41' from sysibm.sysdummy1;

给你'A'.因此，您可以将其放在 for 循环中，并循环遍历每对十六进制字符以到达原始字符串.或者您可以编写自己的 unhex 函数.

gives you 'A'. So you can put that in a for loop and loop through each pair of hex characters to arrive at your original string. Or you can write your own unhex function.

取自 dbforums.com/db2/1627076-display-hex-columns.html(2020 年 11 月原始源链接现在是垃圾网站)

Taken from dbforums.com /db2/1627076-display-hex-columns.html (edit Nov 2020: original source link is now a spam site)

这篇关于在 DB2 上将 HEX 值转换为 CHAR的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！