SQL 进制间如何转换

?

--10进制转16进制

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)