SQL 进制间如何转换
create function inttohex(@int10 int)
returns varchar(8)
begin
declare @int10 int
declare @str16 nvarchar(8)
set @str16=''
if(@int10>0)
begin
while @int10>0
begin
set @str16=substring('0123456789ABCDEF',@int10%16+1,1)+@str16
set @int10=@int10/16
end
end
else
begin
set @str16='0'
end
return @str16
end
--16进制转10进制
create function hextoint(@str16 varchar(8))
returns int
begin
declare @int10 int
declare @i int
set @int10=0
set @i=1
while @i<=len(@str16)
begin
set @int10=@int10+
convert(int,
(case when substring(@str16,@i,1)<='9' then substring(@str16,@i,1)
when substring(@str16,@i,1)<='A' then '10'
when substring(@str16,@i,1)<='B' then '11'
when substring(@str16,@i,1)<='C' then '12'
when substring(@str16,@i,1)<='D' then '13'
when substring(@str16,@i,1)<='E' then '14'
when substring(@str16,@i,1)<='F' then '15' end )) * power(16,len(@str16)-@i)
set @i=@i+1
end
return @int10
end
CREATE FUNCTION hextoint(@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
--10进制转2进制
create function f_int2bin(@i int)
returns varchar(1000)
as
begin
declare @s varchar(1000)
set @s=''
while @i>0
select @s=cast(@i%2 as varchar) @s
,@i=@i/2
return(@s)
end
go
--16进制字符转为2进制字符串
CREATE FUNCTION hextobinary(@str CHAR(1))
RETURNS VARCHAR(4)
AS
BEGIN
DECLARE @var VARCHAR(4)
SET @var=CASE
WHEN @str='0' THEN '0000'
WHEN @str='1' THEN '0001'
WHEN @str='2' THEN '0010'
WHEN @str='3' THEN '0011'
WHEN @str='4' THEN '0100'
WHEN @str='5' THEN '0101'
WHEN @str='6' THEN '0110'
WHEN @str='7' THEN '0111'
WHEN @str='8' THEN '1000'
WHEN @str='9' THEN '1001'
WHEN @str='A' THEN '1010'
WHEN @str='B' THEN '1011'
WHEN @str='C' THEN '1100'
WHEN @str='D' THEN '1101'
WHEN @str='E' THEN '1110'
WHEN @str='F' THEN '1111'
END
RETURN @var
END
--16进制字符串转为2进制字符串
DECLARE @str VARCHAR(50)
DECLARE @res VARCHAR(500)
DECLARE @i INT
SET @i=1
SET @str='80'
SET @res=''
WHILE @i，3
BEGIN
SET @res=@res+dbo.hextobinary(substring(@str,@i,1))
SET @i=@i+1
END
PRINT @res
--10进制转为16进制
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)
