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.
DB2 has built-in encoding/decoding.
For OPs question, use....
select CAST(ColumnName as char(20) CCSID 37) as ColumnName from TableName where SomeConditionExists
http://www-01.ibm.com/support/knowledgecenter/SSEPEK_10.0.0/com.ibm.db2z10.doc.intro/src/tpc/db2z_introcodepage.dita
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 dbforums.com /db2/1627076-display-hex-columns.html (edit Nov 2020: original source link is now a spam site)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With