问题描述
与 SQL Server
到 DB2
的数据复制有关,我有以下问题:
在 DB2
我有一个表包含(简单)两列: COL1
和 COL2
。
COL1
被定义为 CHAR(20)
。 COL2
定义为 CHAR(10)
。
通过将字符串转换为十六进制,例如,从SQL复制 COL1
code>abcdefghij to 6162636465666768696A
或1111111111
至31313131313131313131
,使用以下SQL查询:
CONVERT char(20),cast(@InputString as binary)2)
其中 @InputString
将是abedefghij
。
换句话说,COL1包含十六进制值,但是作为一个字符串(对不起,如果措辞不正确)。
我需要将十六进制值转换回一个字符串,并将该值转换为 COL2
。
SQL查询在 DB2
转换?我知道如何在 SQL Server
上执行此操作,但不能在 DB2
中。
$注意:使用
0x
未预先确定十六进制值的原因是因为中使用样式2 CONVERT
语句。 select hex('A' )from sysibm.sysdummy1;
返回41.
和
从sysibm.sysdummy1中选择x'41';
给你'A'。所以你可以把它放在中
循环,并循环遍历每一对十六进制字符,以获得原来的字符串。或者您可以编写自己的unhex功能。
取自
In connection with data replication from SQL Server
to DB2
I have the following question:
On DB2
I have a table containing (for simplicity) two columns: COL1
and COL2
.
COL1
is defined as CHAR(20)
. COL2
is defined as CHAR(10)
.
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)
where @InputString
would be "abedefghij"
.
In other words COL1 contains the hex value, but as a string (sorry if the wording is incorrect).
I need to convert the hex value back to a string and put this value into COL2
.
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
.
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;
returns 41.and
select x'41' from sysibm.sysdummy1;
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.
Taken from http://www.dbforums.com/db2/1627076-display-hex-columns.html
这篇关于将HEX值转换为DB2上的CHAR的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!