Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Arrow Unanswered: Hexadecimal string to Integer number (light SQL)

    I have used Hexadecimal to Double in 1-st step, because for negative results,
    for example for string 'FFFFFFFF', where result is -1
    we have the arithmetic overflow as result if we'll try to translate to integer number directly:

    Code:
    with input (hexstr) as
    (select '12345EA' from sysibm.sysdummy1
    )
    ,
    hex2dbl(hexstr, hexrmd, dblnmbr) as
    (
    select hexstr, ucase(hexstr), double(0.) 
    from input 
    union all 
    select hexstr, substr(hexrmd, 2) , 
    dblnmbr + 
    power(16, length(hexrmd) - 1) * 
    
    case when substr(hexrmd, 1, 1) between '0' and '9' 
    then double(substr(hexrmd, 1, 1)) 
    when substr(hexrmd, 1, 1) = 'A' then 10.
    when substr(hexrmd, 1, 1) = 'B' then 11.
    when substr(hexrmd, 1, 1) = 'C' then 12.
    when substr(hexrmd, 1, 1) = 'D' then 13.
    when substr(hexrmd, 1, 1) = 'E' then 14.
    when substr(hexrmd, 1, 1) = 'F' then 15.
    else  nullif(0, 0)
    end
    
    from   hex2dbl
    where  length(hexrmd) >= 1
    )
    ,
    hex2int(hexstr, intfromhex) as
    (select hexstr,
    case
    when dblnmbr <= 2147483647 then int(dblnmbr) 
    else int(dblnmbr - 2 * 2147483648) 
    end 
    from hex2dbl 
    where length(hexrmd) = 0
    ) 
    select * from hex2int
    The result has to be:
    HEXSTR INTFROMHEX
    12345EA 19088874
    Thanks, Lenny

  2. #2
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963
    What interesting for Input = hex('Hell') we'll have:

    HEXSTR INTFROMHEX
    C8859393 -930770029
    where result consist two 7s inside.
    Maybe in Hell is not so bad ?

    Lenny

  3. #3
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963
    For trick lovers I can present another solution:

    Code:
    with input (hexstr) as
    (select '12345EA' from sysibm.sysdummy1
    )
    ,
    hex2dbl(hexstr, hexrmd, dblnmbr) as
    (
    select hexstr, ucase(hexstr), double(0.) 
    from input 
    
    union all 
    
    select hexstr, substr(hexrmd, 2) , 
    dblnmbr + 
    power(16, length(hexrmd) - 1) * 
    case Locate(substr(hexrmd, 1, 1), '0123456789ABCDEF')
    when 0 then nullif(0, 0)
    else Locate(substr(hexrmd, 1, 1), '0123456789ABCDEF') - 1 
    end
      from  hex2dbl
    where  length(hexrmd) >= 1
    )
    ,
    hex2int(hexstr, intfromhex) as
    (select hexstr,
    case
    when dblnmbr <= 2147483647 then int(dblnmbr) 
    else int(dblnmbr - 2 * 2147483648) 
    end 
    from hex2dbl 
    where length(hexrmd) = 0
    )
     select * from hex2int
    Lenny

  4. #4
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963
    Finally I'd like to show to you solution of the opposite task:

    Find a hexadecimal string from integer number.

    Result you can check, using the previous SQL:


    Code:
    with input(intnmbr) as
    (select int(2009911) from sysibm.sysdummy1
    )
    ,
    int2hex (intnmbr, remdbl, hexstr) as
    (
    select 
    intnmbr, 
    int(case when intnmbr >= 0  then intnmbr
             else intnmbr + 2 * 2147483648 
        end),
    varchar('', 100)
    from input   
    
    union all
    select 
    intnmbr, int(remdbl / 16),
    case  when mod(remdbl, 16) between 0 and 9 
                                     then varchar(mod(remdbl, 16)) 
          when mod(remdbl, 16) = 10 then 'A'
          when mod(remdbl, 16) = 11 then 'B' 
          when mod(remdbl, 16) = 12 then 'C' 
          when mod(remdbl, 16) = 13 then 'D' 
          when mod(remdbl, 16) = 14 then 'E'  
          when mod(remdbl, 16) = 15 then 'F' 
    end || hexstr
    from int2hex 
    where remdbl > 16
    
    union all
    select 
    intnmbr, nullif(0, 0),
    case  when int(remdbl) between 0 and 9 
                                     then varchar(int(remdbl))
          when int(remdbl) = 10 then 'A'
          when int(remdbl) = 11 then 'B' 
          when int(remdbl) = 12 then 'C' 
          when int(remdbl) = 13 then 'D' 
          when int(remdbl) = 14 then 'E'  
          when int(remdbl) = 15 then 'F' 
    end || hexstr 
    from int2hex 
    where remdbl < 16 
    
    ) 
    select  intnmbr, hexstr 
      from  int2hex
      where remdbl is null
    Result:
    INTNMBR HEXSTR
    2009911 1EAB37


    Lenny

  5. #5
    Join Date
    Jul 2009
    Posts
    150
    Quote Originally Posted by Lenny77
    What interesting for Input = hex('Hell') we'll have:

    HEXSTR INTFROMHEX
    C8859393 -930770029


    where result consist two 7s inside.
    Maybe in Hell is not so bad ?

    Lenny
    And 88 --> 77 where 88 is evil number.
    Not so bad !
    But 93 present in both parts.... What does it mean ?
    Last edited by DB2Plus; 09-11-09 at 21:49.

Posting Permissions

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