-- In SQL6 & above. This requires a table of numbers.
-- There are more examples using the numbers table under the
-- SQL6xScripts in my website. The structure of the table is
-- CREATE TABLE Numbers( number int ) 
-- where number is between 1 and 255 or so.
declare @t varchar (8)
select @t = 'FFFFFFFF'
select sum(
      case lower( substring( hexstr , number , 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
        when 'f' then 15
      end *  convert( decimal( 28 , 0 ) , power( 16 , number - 1 ) )
)
from 
(
select reverse( @t  ) as hexstr
) as h1
join
Numbers n
on number between 1 and len( hexstr )
go

-- In SQL6x & above
declare @hexstr varchar( 10 ) , @intval int
select @hexstr = '0x2A'
exec('declare intcur cursor for select  convert( int , ' + @hexstr + ' )')
open intcur
fetch intcur into @intval
deallocate intcur
select @intval
go

-- In SQL70
declare @hexstr varchar( 10 ) , @intval int , @cmdstr nvarchar( 255 )
select @hexstr = '0x2A'
Select @cmdstr = N'Select @intval = convert( int , ' + @hexstr + ' )'
Exec sp_ExecuteSql @cmdstr, N'@intval Int Out', @intval Out
select @intval
go

This page was last updated on May 01, 2006 04:28 PM.