Convert Hex Value to Char on Db2

Convert HEX value to CHAR on DB2

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)

How to cast hex data string to a string db2 sql

I was able to take your shortened hex string and convert is to a valid EBCDIC string.
The problem I ran into is that the original hex code you receive comes in UTF-16LE (Thanks Tom Blodget). IBM's CCSID system does not have a distinction between UTF-16BE and UTF-16LE so I am at a loss there on how to convert it properly.

If it is in UTF-8 as you generated later, the following would work for you. It's not the prettiest but throw it in a couple functions and it will work.

Create or replace function unpivothex (in_ varchar(30000))
    returns table (Hex_ char(2), Position_ int)
    return
    with returnstring (ST , POS )
    as 
    (Select substring(STR,1,2), 1
    from table(values in_) as A(STR)
    union all
    Select nullif(substring(STR,POS+2,2),'00'), POS+2
    from returnstring, table(values in_) as A(STR)
    where POS+2 <= length(in_)
    )
    Select ST, POS 
    from returnstring
    ;

Create or replace function converthextostring
   (in_string char(30000))
   returns varchar(30000)
   return
   (select listagg(char(varbinary_format(B.Hex_),1)) within group(order by In_table.Position_)
   from table(unpivothex(upper(in_string))) in_table
   join table(unpivothex(hex(cast('ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz ' as char(53) CCSID 1208)))) A on In_table.Hex_ = A.Hex_
   join table(unpivothex(hex(cast('ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz ' as char(53) CCSID 37)))) B on A.Position_ = B.Position_
   );

Here is a version if you're not on at least V7R2 TR6 or V7R3 TR2.

Create or replace function converthextostring
   (in_string char(30000))
   returns varchar(30000)
   return
   (select xmlserialize(
             xmlagg(
               xmltext(cast(char(varbinary_format(B.Hex_),1) as char(1) CCSID 37)) 
             order by In_table.Position_) 
           as varchar(30000))
   from table(unpivothex(upper(in_string))) in_table
   join table(unpivothex(hex(cast('ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz ' as char(53) CCSID 1208)))) A on In_table.Hex_ = A.Hex_
   join table(unpivothex(hex(cast('ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz ' as char(53) CCSID 37)))) B on A.Position_ = B.Position_
   );

DB2 query results in Hex format -- Need Character/String

Using your query, you can cast a string with another CCSID, eg :

select cast(ccdata as char(14) CCSID 37) from ORCTL where ccctlk = 'BUYRAK'

How to convert hexadecimal to decimal on DB2

AFAIK, there is no such single function built into DB2 that would perform that conversion, but there is a blog post showing how to define such a function. The following function is taken from that article:

--#SET TERMINATOR @
CREATE OR REPLACE FUNCTION HEX2INT(str VARCHAR(8))
RETURNS INTEGER
SPECIFIC HEX2INT
DETERMINISTIC NO EXTERNAL ACTION CONTAINS SQL
BEGIN ATOMIC
  DECLARE res INTEGER  DEFAULT 0;
  DECLARE pos INTEGER DEFAULT 1;
  DECLARE nibble CHAR(1);
  WHILE pos <= LENGTH(str) DO
    SET nibble = SUBSTR(str, pos, 1);
    SET res = BITOR(CASE WHEN BITAND(res, 134217728) != 0
                         THEN BITOR(16 * BITANDNOT(res, 134217728),
                                    -2147483648)
                         ELSE 16 * res END,
                    CASE nibble
                         WHEN '0' THEN 0
                         WHEN '1' THEN 1
                         WHEN '2' THEN 2
                         WHEN '3' THEN 3
                         WHEN '4' THEN 4
                         WHEN '5' THEN 5
                         WHEN '6' THEN 6
                         WHEN '7' THEN 7
                         WHEN '8' THEN 8
                         WHEN '9' THEN 9
                         WHEN 'A' THEN 10
                         WHEN 'a' THEN 10
                         WHEN 'B' THEN 11
                         WHEN 'b' THEN 11
                         WHEN 'C' THEN 12
                         WHEN 'c' THEN 12
                         WHEN 'D' THEN 13
                         WHEN 'd' THEN 13
                         WHEN 'E' THEN 14
                         WHEN 'e' THEN 14
                         WHEN 'F' THEN 15
                         WHEN 'f' THEN 15
                         ELSE RAISE_ERROR('78000', 'Not a hex string') 
                         END),
        pos = pos + 1;
  END WHILE;
  RETURN res;
END
@
--#SET TERMINATOR ;

There are more functions for various conversion operations described.

Character with Bit/hex confusion in DB2

What are you using to call the stored procedure?

What version and release of Db2 for i?

In the Run SQL Scripts component of IBM ACS or the older Access for Windows, string literals in your statements are treated as varchar.

Thus the CAST('0213725501A421D384233E5001' as char(26)) makes sense. What doesn't is the error message. Normally, you'd get a procedure not found error as the Db is looking for a procedure named PROGRAM.GET_TASKT_ID_BY_TASK_WEB_IDENTIFIER that takes a varchar parameter and the only thing that exists is a procedure that takes a char(26).

IBM's tools have gotten better at implicitly converting when needed. But I usually go with an explicit conversion when testing manually (as you've done here). Or I just make the parms varchar to start. And convert to character within the procedure if needed.

The char/varchar difference doesn't usually matter to the client code, as it can be specific in it's type definitions. It's only a factor for interactive tools like ACS that are executing dynamic statements.

---

Related Topics