Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2014
    Posts
    10

    Question Answered: How Unhex in db2 ?

    I am stuck in a problem that I stored data in a table using hex function.

    Insert into hex (Hexdata) values(Hex('KSHITIJ'));
    it stored in the format Like
    '4B53484954494A'

    Now i can extract it to original format by using this query:
    select X'4B53484954494A' from sysibm.sysdummy1;

    but i cant do this

    select X'<column name>' from hex;

    SO is there any way to retrieve data from hex table in the character format. i.e. can we directly unhex the value??

    (apart from it I tried to convert those Hex data into ASCII and Use of CHR command but it seems I failed to get data)

    Please Help.

  2. Best Answer
    Posted by mark.b

    "You can create a simple java function for this.

    Code:
    public class Convert {
      public static String byte2char(byte[] b, String enc) throws java.io.UnsupportedEncodingException {
        return new String(b, enc);
      }
    }
    Place the Convert.class file which you get after the Convert.java file compilation into the sqllib/function directory on the db2 server.
    Register the function:

    Code:
    create function byte2char(bytes varchar(4000) for bit data, enc varchar(16))
    RETURNS varchar(4000)
    LANGUAGE JAVA
    EXTERNAL NAME 'Convert.byte2char'
    FENCED THREADSAFE
    NO SQL
    NOT NULL CALL
    NO EXTERNAL ACTION
    ALLOW PARALLEL
    PARAMETER STYLE JAVA;
    Call it:
    Code:
    values byte2char(hextoraw(Hex('KSHITIJ')), 'UTF-8')
    "


  3. #2
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    You can create a simple java function for this.

    Code:
    public class Convert {
      public static String byte2char(byte[] b, String enc) throws java.io.UnsupportedEncodingException {
        return new String(b, enc);
      }
    }
    Place the Convert.class file which you get after the Convert.java file compilation into the sqllib/function directory on the db2 server.
    Register the function:

    Code:
    create function byte2char(bytes varchar(4000) for bit data, enc varchar(16))
    RETURNS varchar(4000)
    LANGUAGE JAVA
    EXTERNAL NAME 'Convert.byte2char'
    FENCED THREADSAFE
    NO SQL
    NOT NULL CALL
    NO EXTERNAL ACTION
    ALLOW PARALLEL
    PARAMETER STYLE JAVA;
    Call it:
    Code:
    values byte2char(hextoraw(Hex('KSHITIJ')), 'UTF-8')
    Regards,
    Mark.

  4. #3
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    why not just a simple update?
    replace ascii with whatever codeset you want
    Code:
    update tablex
    set columny = ascii(columny)
    or select the same way

    Code:
    select ebcdic(columny)
       from tablex
    Dave

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •