# Thread: Hexadecimal string to Integer number (light SQL)

1. Registered User
Join Date
Jul 2009
Location
NY
Posts
963

## 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. Registered User
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. Registered User
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. Registered User
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. Registered User
Join Date
Jul 2009
Posts
150
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 20: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
•