Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2012
    Posts
    29

    Unanswered: hexadecimal value in varchar for bit data

    Hi,
    I have a column of type VARCHAR (512) FOR BIT DATA that is initialized with x'00000000'.

    Then I have integer values that I want to save in this column, for example I'd like to save 1 as x'00000001'.

    What can I use to achieve this?

    Thanks

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I thought that it might be not so difficult technically, but a little complicated.

    The following answer must be not optimal. But, it would work.

    (1) Create two user-defined-functions.
    Code:
    ------------------------------ Commands Entered ------------------------------
    CREATE OR REPLACE FUNCTION hex_bigendian
    ( in_int INTEGER )
      RETURNS CHAR(8)
      CONTAINS SQL
      NO EXTERNAL ACTION
      DETERMINISTIC
    RETURN
    CASE LEFT( HEX(1) , 2 )
    WHEN '00' THEN
         HEX(in_int)
    WHEN '01' THEN
         TRANSLATE('78563412' , HEX(in_int) , '12345678')
    ELSE '**N.A.**'
    END
    ;
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    Note: Replace the statement-termination-character to "!".
    Code:
    ------------------------------ Commands Entered ------------------------------
    CREATE OR REPLACE FUNCTION X2C (HX VarChar(768))
     RETURNS VarChar(256)
     SPECIFIC X2C
     LANGUAGE SQL
     CONTAINS SQL
     NO EXTERNAL ACTION
     DETERMINISTIC
    -- Input format:
    --               xx[xx[...]]
    --               xx[,xx[...]]
    --               xx[ xx[...]]
    --
    BEGIN ATOMIC
    DECLARE HXN     VARCHAR(512);
    DECLARE HXLen   Integer;
    DECLARE XDigits CHAR(16) DEFAULT '123456789ABCDEF';
    DECLARE RetVal  VARCHAR(256);
    DECLARE Pos     Integer;
    
    SET HXN = UCASE(REPLACE(REPLACE(HX,',',''),' ',''));
    SET HXLen = LENGTH(HXN);
    IF MOD(HXLen,2) <> 0 THEN
       SIGNAL SQLSTATE 'UX001' SET MESSAGE_TEXT = 'Input data format error';
    END IF;
    
    SET (Pos, RetVal) = (1, '');
    WHILE Pos <= HXLen DO
       SET RetVal
         = RetVal
           || CASE SUBSTR(HXN,Pos,  2)
              WHEN '00' THEN
                   x'00'
              ELSE CHR( (LOCATE(SUBSTR(HXN,Pos,  1),XDigits))*16
                       + LOCATE(SUBSTR(HXN,Pos+1,1),XDigits)     )
              END;
       SET Pos = Pos + 2;
    END WHILE;
    
    RETURN RetVal;
    END!
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.

    (2) use the UDFs to generate a hex value from a source integer value(source_int), like...
    Code:
    x2c( hex_bigendian(source_int) )

    Example:
    Code:
    ------------------------------ Commands Entered ------------------------------
    SELECT source_int
         , HEX(source_int)                              AS hex_sql
         , hex_bigendian(source_int)                    AS bigendian
         , CHAR( x2c( hex_bigendian(source_int) ) , 4 ) AS varchar
         , HEX( x2c( hex_bigendian(source_int) ) )      AS hex_varchar
     FROM  (VALUES 0 , 1 , 1234, 987654321
                 , -1 , - 1234 , -987654321
           ) t(source_int)
    ;
    ------------------------------------------------------------------------------
    
    SOURCE_INT  HEX_SQL  BIGENDIAN VARCHAR HEX_VARCHAR                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     
    ----------- -------- --------- ------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
              0 00000000 00000000          00000000                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
              1 01000000 00000001          00000001                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
           1234 D2040000 000004D2          000004D2                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
      987654321 B168DE3A 3ADE68B1  :�h    3ADE68B1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
             -1 FFFFFFFF FFFFFFFF  ��    FFFFFFFF                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
          -1234 2EFBFFFF FFFFFB2E  ��    FFFFFB2E                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
     -987654321 4F9721C5 C521974F  �!     C521974F                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
    
      7 record(s) selected.
    Last edited by tonkuma; 03-01-12 at 15:38. Reason: Replace sample expression(2) and "Example:". Replace function-body of hex_bigendian.

  3. #3
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Stupid question from my side: why do you want to stored integers as binary data and not as integers?
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  4. #4
    Join Date
    Jan 2012
    Posts
    29
    To stolze:
    I need varchar for bit data because I have to store multiple numbers, separated by comma. For example, I can store "00000001,00000012,00000034".This comes from a porting, so I wouldn't change this logic if not strictly necessary.

    To tonkuma:
    Thank you very much for the UDFs, really appreciate.

Posting Permissions

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