-----二进制转换十进制----------------- 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
create table tb(num int,HEX nvarchar(10)) insert into tb(num) select 9 insert into tb(num) select 13 insert into tb(num) select 157 insert into tb(num) select 16 go create function itoh(@i as int)returns nvarchar(10) begin declare @s nvarchar(10),@j int set @s='' if @i=0 set @s='0' while @i>0 begin set @j=@i % 16 if @j<10 set @s=convert(varchar(1),@j)+@s else set @s=char(@j+55)+@s set @i=@i/16 end return @s end go update tb set hex=dbo.itoh(num) select * from tb go drop table tb drop function dbo.itoh /* num HEX ----------- ---------- 9 9 13 D 157 9D 16 10(4 行受影响)*/
--十进制转为十六进制DECLARE @binary varbinary(255), @str_return varchar(255) SELECT @binary = CONVERT(varbinary(255),29327795562177529) EXEC master.dbo.xp_varbintohexstr @binary, @str_return OUTPUT SELECT 结果 = reverse(@str_return)/* 结果 __________________________ 008613974878369F10000011x0 */--十六进制转为十进制 CREATE FUNCTION dbo.f_hex_dec(@s varchar(16)) RETURNS bigint AS BEGIN --作者:pbsql --参数不得含'0'~'9'、'a'~'f'、'A'~'F'之外的任意字符(首尾空格除外),否则返回0 DECLARE @i int,@result bigint SELECT @i=0,@result=0,@s=RTRIM(LTRIM(UPPER(REVERSE(@s)))) WHILE @i<LEN(@s) BEGIN IF SUBSTRING(@s,@i+1,1) not between '0' and '9' and SUBSTRING(@s,@i+1,1) not between 'A' and 'F' BEGIN SELECT @result=0 break END SELECT @result=@result+(CHARINDEX(SUBSTRING(@s,@i+1,1),'0123456789ABCDEF')-1)*POWER(16,@i),@i=@i+1 END RETURN @result END GO
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
insert into tb(num) select 9
insert into tb(num) select 13
insert into tb(num) select 157
insert into tb(num) select 16
go
create function itoh(@i as int)returns nvarchar(10)
begin
declare @s nvarchar(10),@j int
set @s=''
if @i=0
set @s='0'
while @i>0
begin
set @j=@i % 16
if @j<10
set @s=convert(varchar(1),@j)+@s
else
set @s=char(@j+55)+@s
set @i=@i/16
end
return @s
end
go
update tb set hex=dbo.itoh(num)
select * from tb
go
drop table tb
drop function dbo.itoh
/*
num HEX
----------- ----------
9 9
13 D
157 9D
16 10(4 行受影响)*/
@binary varbinary(255),
@str_return varchar(255) SELECT
@binary = CONVERT(varbinary(255),29327795562177529)
EXEC master.dbo.xp_varbintohexstr @binary, @str_return OUTPUT
SELECT
结果 = reverse(@str_return)/*
结果
__________________________
008613974878369F10000011x0
*/--十六进制转为十进制
CREATE FUNCTION dbo.f_hex_dec(@s varchar(16))
RETURNS bigint
AS
BEGIN
--作者:pbsql
--参数不得含'0'~'9'、'a'~'f'、'A'~'F'之外的任意字符(首尾空格除外),否则返回0
DECLARE @i int,@result bigint
SELECT @i=0,@result=0,@s=RTRIM(LTRIM(UPPER(REVERSE(@s))))
WHILE @i<LEN(@s)
BEGIN
IF SUBSTRING(@s,@i+1,1) not between '0' and '9' and SUBSTRING(@s,@i+1,1) not between 'A' and 'F'
BEGIN
SELECT @result=0
break
END
SELECT @result=@result+(CHARINDEX(SUBSTRING(@s,@i+1,1),'0123456789ABCDEF')-1)*POWER(16,@i),@i=@i+1
END
RETURN @result
END
GO