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=27set @s=''select @s=cast(@i%2 as varchar)+@s,@i=@i/2from (select number from master.dbo.spt_values where type='p'  and number<10 and power(2,number)<@i)Korder by  number desc select @s結果：11011-----十進位轉換八進位-----------------declare @i int,@s varchar(10)set @i=27set @s=''select @s=cast(@i%8 as varchar)+@s,@i=@i/8from (select number from master.dbo.spt_values where type='p'  and number<10 and power(8,number)<@i)Korder by  number desc select @s結果：33-----十進位轉換十六進位-----------------declare @i int,@s varchar(10)set @i=2379set @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/16from (select number from master.dbo.spt_values where type='p'  and number<10 )Korder by  number desc select @s結果:94B