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 dbforums.com /db2/1627076-display-hex-columns.html (edit Nov 2020: original source link is now a spam site)