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 )

/*
结果
__________________________
008613974878369F10000011x0
*/

 

 

转载于:https://www.cnblogs.com/MyFavorite/archive/2010/08/11/1797412.html