Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
2.1k views
in Technique[技术] by (71.8m points)

sql - Convert HEX value to CHAR on DB2

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.

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)
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)


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...