How to cast hex data string to a string db2 sql

Question

How would you decode a hex string to get the value in text format by using a select statement?

For example my data in hex is:

4f004e004c005900200046004f00520020004200410043004b002d005500500020004f004e0020004c004500560045004c0020004f004e004500200046004f00520020004300520041004e004500530020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020000000

I want to decode it to get the string value using a select statement. The value of the above is "ONLY FOR BACK-UP ON LEVEL ONE FOR CRANES"

what I have tried is :

    SELECT CAST('4f004e004c005900200046004f00520020004200410043004b002d005500500020004f004e0020004c004500560045004c0020004f004e004500200046004f00520020004300520041004e004500530020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020000000' 
    AS VARCHAR(30000) CCSID 37) from myschema.atable

The above sql returns the exact same hex string and not the decoded text string of "ONLY FOR BACK-UP ON LEVEL ONE FOR CRANES" what I expected.

Is it possible to do this with a cast? If it is what will the syntax be?

My problem that I have is a system stores text data in a blob field and I want to use a select statement to see what the text data is in the blob field.

Db : Db2 on Ibm

Edit:

I have managed to covert the string to the hex value by using :

    select hex(cast('ONLY FOR BACK-UP ON LEVEL ONE FOR CRANES' as varchar(100) ccsid 1208))
FROM myschema.atable

This gives me the string in hex :

4F4E4C5920464F52204241434B2D5550204F4E204C4556454C204F4E4520464F52204352414E4553

Now somehow I need to do the inverse and get the value.

Thanks.

Edit

Using the answer from Daniel Lema, I tried using the unhex function but my result that I got was :

|+<ßã|êâ ä.í&|+<áîá<|+áã|êäê +áë

Is this something to do with a CSSID? Or how should I convet the above to a readable string?

This is the table field definition if it will help the field with my data in is GDTXFT a BLOB :

Solution 1

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_
   );

Solution 2

I tried the following solution I found published by Marcin Rudzki at Convert HEX value to CHAR on DB2, tested in my own Db2 for LUW v11 with a small modification.

the solution consists on creating a function just as Marcin suggested:

CREATE FUNCTION unhex(in VARCHAR(32000) FOR BIT DATA)
RETURNS VARCHAR(32000) 
LANGUAGE SQL
CONTAINS SQL
DETERMINISTIC NO EXTERNAL ACTION
BEGIN ATOMIC
RETURN in;
END

To test the solution, lets create an HEXSAMPLE table with a HEXSTRING column loaded with the string representation of a HEX sequence:

INSERT INTO HEXSAMPLE (HEXSTRING) VALUES ('4F4E4C5920464F52204241434B2D5550204F4E204C4556454C204F4E4520464F52204352414E4553')

Then exec the following query (and here it is different from the original proposal):

SELECT UNHEX(CAST(HEXTORAW(HEXSTRING) AS VARCHAR(2000) FOR BIT DATA)) as TEXT, HEXSTRING FROM HEXSAMPLE

With result:

 TEXT                                           HEXSTRING
 ----------------------------------------   --------------------------------------------------------------------------------
 ONLY FOR BACK-UP ON LEVEL ONE FOR CRANES   4F4E4C5920464F52204241434B2D5550204F4E204C4556454C204F4E4520464F52204352414E4553

I hope someone else can find a more direct solution. Also, if someone can explain why it works, it will be very interesting.

Comments

[Nifriz]

I don't think it's possible what do y want, you want to transform an ASCII or EBCDIC value into a corresponding CHAR Value, and SQL can only transform a Type, not translate it...

[Renier]

yes I want the ASCII value, so what you are saying you should create a function to do this?

[Nifriz]

Yes, I suppose you need a function, but wait someone else, maybe there's another way

[MandyShaw]

There's a popular answer here stackoverflow.com/questions/7913300/… which looks to be managing to do this via a cast to CHAR (rather than VARCHAR) - have you tried that?

[Mark Barinstein]

I don't have db2 for i at hand, but have you tried select varchar_format(myhexcol) from mytab? It works for db2 for luw, but with spaces between letters...

[Renier]

@MandyShaw I tried using CHAR but it gave me the same hex string.

[Renier]

@MarkBarinstein I tried using varchar_format the result was the same hex string.

[Renier]

I tried using the function but I get a different result than you please check my edit update to my question.

[Renier]

Thanks, will try to give this a go, I have updated my question to show you the table fields and types, the type is actually a blob and I get the hex string when I do a select from the table. please see edit update. Maybe I am doing this wrong... I am not in control with regards to the system inserting the data I am just trying to create a report to display the content(text) that is stored in the field.

[Renier]

I would like to know how to call the cvtch fcuntion in a UDF without a ILE wrapper, if you get time I would appreciate it, I will also be trying to figure it out.

[Renier]

In what library will CVTCH be in? I am assuming I need to specify where CVTCH is in the UDF

[Daniel Lema]

Sorry... I didn't realize that you were working on DB2 for IBM i. I have some ideas but I don't have an IBM i at hand to try before answering, so, this time I pass. By the way: HEXTORAW is not documented on IBM i Knowledge Center, so, probably it is not officially supported (yet).

[Charles]

cvtch is actually an MI builtin, all you need is extproc('cvtch') and the compilers will be able to resolve it.

[Tom Blodget]

" x'00' byte after each letter" would strongly suggest that the encoding is UTF-16LE.

[Renier]

Thanks for the functions, I am trying to implement them, the first function compiles and gets created, but "converthextostring" is givving me syntax issues with the "group" statement, "SQL State: 42601 Vendor Code: -199 Message: [SQL0199] Keyword GROUP not expected. Valid tokens: , FROM INTO. Cause . . . . . : The keyword GROUP was not expected here. A syntax error was detected at keyword GROUP" Any ideas how to fix it or a different way to write this?

[Michael S.]

Which version of IBMi are you running? I can check the listagg functionality for your version. Latest version is V7R3.

[Michael S.]

listagg was released for V7R2 TR6 or V7R3 TR2. Unfortunately, V7R1 is no longer supported by IBM. I've updated my answer though for an alternative to listagg.

[Michael S.]

@renier, I've made some changes to handle the UTF-16LE problem from your original question as the prior version did not account for it. Of course this will only work if your messages only contain upper and lower case A-Z. If you want to add numbers, just include them in the second function's list.

[Renier]

Thank you I do appreciate it.

[M. A. Wheadon]

I am using DB2 on i and had a problem like this. We have a legacy DB where a column is declared as char(3) but the (RPG) programmer uses it to store 24 flags using the bits. This is easy to read (JDBC) using 'hex(flags)' - the Java program receives it as a String 6 long with hex digits. Writing was a problem. The x'ABCDEF' solution above works great, but I couldn't find a way to get that to work with a prepared statement (Spring data JPA). Eventually I found that 'cast(hextoraw(:hexval) as char(3))' worked. hextoraw appears in DB2 10.5 and Db2 for i SQL 7.3 manuals.