sql进制之间的转换，sql进制转换

sql进制之间的转换，sql进制转换

-----二进制转换十进制-----------------
select sum(data1)
from (  select substring('11011', number, 
1)*power(2,len('11011')-number)data1 
          from (select number from master.dbo.spt_values where type='p')K
          where number <= len('11011')
      ) L
结果：
27
-----八进制转换十进制-----------------
select sum(data1)
from (  select substring('1234567', number, 1)*power(8,len('1234567')-number)data1 
       from (select number from master.dbo.spt_values where type='p')K
       where number <= len('1234567')
     ) L
结果：
342391
-----十六进制转换十进制-----------------
select sum(data1)
from (   select case upper(substring('4eb7', number, 1)) when 'A' then 10 
                                                    when 'B' then 11
                                                    when 'C' then 12 
                                                    when 'D' then 13 
                                                    when 'E' then 14
                                                    when 'F' then 15 
                else substring('4eb7', number, 1)
                end* power(16, len('4eb7') - number) data1 
         from (select number from master.dbo.spt_values where type='p')K
         where number <= len('4eb7')
     ) L
结果：
20151
  
-----十进制转换二进制-----------------
declare @i int,@s varchar(10)
set @i=27
set @s=''
select @s=cast(@i%2 as varchar)+@s,@i=@i/2
from (select number from master.dbo.spt_values where type='p'  and number<10 and power(2,number)<@i)K
order by  number desc 
select @s
结果：
11011
-----十进制转换八进制-----------------
declare @i int,@s varchar(10)
set @i=27
set @s=''
select @s=cast(@i%8 as varchar)+@s,@i=@i/8
from (select number from master.dbo.spt_values where type='p'  and number<10 and power(8,number)<@i)K
order by  number desc 
select @s
结果：
33
-----十进制转换十六进制-----------------
declare @i int,@s varchar(10)
set @i=2379
set @s=''
select @s=case when @i%16 between 10 and 15 then char( @i%16+55) else cast(@i%16 as varchar) end +@s,@i=@i/16
from (select number from master.dbo.spt_values where type='p'  and number<10 )K
order by  number desc 
select @s
结果:
94B