question

tstrole avatar image
tstrole asked

Looking for hex equivalent of db2 hex

![alt text][1]Hello, I'm looking for the equivalent of DB2 statement, select hex('4') from sysibm.sysdummp1, which returns F4 . I have tried : select CONVERT(varbinary(max), 4); -which returns 0x00000004 and select CONVERT(varbinary(max), '4'); -which returns 0x34 What are the differences and is there an alternative method? Tammy [1]: /storage/temp/3744-designated-negative.png
sql server 2012
not-designated-negative.png (10.0 KiB)
designated-negative.png (7.9 KiB)
9 comments
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

KenJ avatar image KenJ commented ·
try grabbing a copy of sp_hexadecimal here - https://support.microsoft.com/en-us/kb/918992 also.... shouldn't hex(4) be 04 rather than F4 (which is 244) - http://online.sfsu.edu/chrism/hexval.html
1 Like 1 ·
tstrole avatar image tstrole commented ·
Thank you, I will check out the sp. I'm recreating a DB complex view, and getting F4 from what is returned in an iSeries navigator query. The Db2 source data is of type char, so I have put the quote around the 4. -select hex('4') from sysibm.sysdummy1 definitely returns F4 in Db2.
0 Likes 0 ·
tstrole avatar image tstrole commented ·
Of note, the hex value of 244 is f4, according the Hex values chart. -?
0 Likes 0 ·
tstrole avatar image tstrole commented ·
I believe, if I add a little more context, it may make more sense. The goal in the iSeries is to "Determine sign from last character by hex value". I have just isolated the character 4 using of of the data. So from this point, does anyone have advice?
0 Likes 0 ·
tstrole avatar image tstrole commented ·
Here is the specific iSeries logic: SUBSTRING(HEX(SUBSTRING(REFNO,9,1)),1,1) IN ('D', 'F') THEN '-'
0 Likes 0 ·
Show more comments

1 Answer

·
tstrole avatar image
tstrole answered
I have found a site ( http://www.simotime.com/asc2ebc1.htm#D192223) that has finally made sense of where my DB2 output is coming from. (see attachment). As you can 4 is EBCDIC that then translates to F4 in hex. I had to realize what I was starting with EBCDIC. This does not resolve my SQL conversion, but at least I can use the chart and now write my own sql conversion based on the iSeries view criteria. Thanks for everyone who provided input!! [1]: /storage/temp/3748-hex.png

hex.png (10.5 KiB)
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.