Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2007
    Posts
    15

    Unanswered: Display Hex columns

    Hi, I'm a db2 newbie and I have a question about how to display my Hex column back to clear text.

    I have a generated column called HexCol using HEX() function.

    I know how to convert the HEX value back to clear text by using
    select x'7465737431' from sysibm.sysdummy1; -- displays test1

    But how do you specify the column name in the above select statement?

    select select x+"'"+HexCol+"'" from myTab ?

    Please help.

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    You can specify your own column names as usual:
    Code:
    select x'7465737431' AS my_column_name from sysibm.sysdummy1
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  3. #3
    Join Date
    May 2003
    Posts
    113
    SELECT "7465737431" FROM MYTAB.

    "7465737431". use double-q here


    Quote Originally Posted by HanbingL
    Hi, I'm a db2 newbie and I have a question about how to display my Hex column back to clear text.

    I have a generated column called HexCol using HEX() function.

    I know how to convert the HEX value back to clear text by using
    select x'7465737431' from sysibm.sysdummy1; -- displays test1

    But how do you specify the column name in the above select statement?

    select select x+"'"+HexCol+"'" from myTab ?

    Please help.

  4. #4
    Join Date
    Dec 2007
    Posts
    15
    I guess I wasn't being clear.

    My question is, how do I unhex a column instead of a hex value.

    I can:
    select x'7465737431' from sysibm.sysdummy1;

    can not:
    select UNHEX(hexcol) from myTab;

    thanks.

  5. #5
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    DB2 provides the function CHR to convert an integer value to a character based on the ASCII. If you don't have plain ASCII, you will have to worry about the code page conversion, which can quickly get messy (and is probably one of the reasons why the function is not available generally).

    The issue with CHR is that it returns only a single character. So you have to loop over the input string. Another issue is that the output of the HEX function returns, well, hex codes. CHR takes an integer in decimal format. Therefore, you will have to convert hex to decimal numbers. Once you know the pieces, tying all this together is straight-forward:
    Code:
    DROP FUNCTION unhex@
    CREATE FUNCTION unhex(in VARCHAR(100) FOR BIT DATA)
       RETURNS VARCHAR(50)
       LANGUAGE SQL
       CONTAINS SQL
       DETERMINISTIC NO EXTERNAL ACTION
       BEGIN ATOMIC
          DECLARE pos INTEGER DEFAULT 1;
          DECLARE hex CHAR(2);
          DECLARE ascii INTEGER;
          DECLARE result VARCHAR(50) DEFAULT '';
          
          IF in IS NULL THEN
             RETURN NULL;
          END IF;
    
          WHILE ( pos < LENGTH(in) ) DO
             SET hex = UPPER(SUBSTR(in, pos, 2));
             SET ascii = CASE SUBSTR(hex, 1, 1)
                            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 'B' THEN 11
                            WHEN 'C' THEN 12
                            WHEN 'D' THEN 13
                            WHEN 'E' THEN 14
                            ELSE 15
                         END * 16 +
                         CASE SUBSTR(hex, 2, 1)
                            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 'B' THEN 11
                            WHEN 'C' THEN 12
                            WHEN 'D' THEN 13
                            WHEN 'E' THEN 14
                            ELSE 15
                         END;
             SET result = result || CHR(ascii);
             SET pos = pos + 2;
          END WHILE;
          RETURN result;
       END@
    Some simple test:
    Code:
    $ db2 "values unhex(hex('123'))"
    
    1
    --------------------------------------------------
    123
    
      1 record(s) selected.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  6. #6
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by HanbingL
    I can:
    select x'7465737431' from sysibm.sysdummy1;

    can not:
    select UNHEX(hexcol) from myTab.
    You'll have to do that outside DB2.
    Just write a function (could be a UDF) which iterates over pairs of characters in the argument (hexcol), computes a number between 0 and 255 from this, and then passes that number to the CHAR scalar function.
    Number to be computed from two hexadecimal digits is 16 times the first plus the second.
    ('a' counts for 10, ..., 'f' counts for 15.)
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

Posting Permissions

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