SQL 实现十进制数和十六进制字符串互相转换

 
0001 use master
0002 go
0003  
0004 if exists(select * from sysobjects where name = N'maker_fInt2Hex')
0005         drop function maker_fInt2Hex
0006 GO
0007 create function maker_fInt2Hex(@num bigint, @hexLength int)
0008         returns varchar(100)
0009 as 
0010 begin 
0011         declare @re varchar(100)
0012         declare @bn bigint, @tempLen int
0013         set @bn = @num
0014         set @re='' 
0015         ---------------------------------------- 
0016         while (@num>0)
0017                 begin 
0018                         set @re=substring('0123456789ABCDEF',@num%16+1,1)+@re
0019                         set @num=@num/16
0020                 end 
0021         ---------------------------------------- 
0022         set @tempLen = @hexLength - len(@re)
0023         while(@tempLen > 0) 
0024                 begin 
0025                         set @re = '0' + @re   
0026                         set @tempLen = @tempLen - 1  
0027                 end 
0028         return(@re)
0029 end 
0030 GO
0031  
0032 if exists(select * from sysobjects where name = N'maker_fHex2Int')
0033         drop function maker_fHex2Int
0034 GO
0035  
0036 create function maker_fHex2Int (@hexS varchar(16))
0037 returns bigint
0038 AS 
0039 begin 
0040         declare @i int, @result bigint, @len int
0041         declare @power bigint
0042         set @power = 16
0043         select @i = 0, @result = 0, @hexS = RTRIM(LTRIM(UPPER(@hexS)))
0044         set @len = len(@hexS) 
0045  
0046         if (@len = 16)
0047                 begin 
0048                         if (ascii(substring(@hexS, 1, 1)) > 55)
0049                                 begin 
0050                                         -- RaisError('超出数据运算范围', 1, 16) 
0051                                         return @result
0052                                 end 
0053                 end 
0054         ------------------------------------------------------- 
0055         while (@i < @len)
0056                 begin 
0057                         if ((substring(@hexS, @len - @i, 1) not between '0' and '9')
0058                                 AND 
0059                                 (substring(@hexS, @len - @i, 1) not between 'A' and 'F'))
0060                                 begin 
0061                                         set @result = 0
0062                                         break;
0063                                 end 
0064                         ---------------------------------------- 
0065  
0066                         set @result = @result + (charindex(substring(@hexS, @len - @i, 1), '0123456789ABCDEF') - 1) * cast(power(@power, @i) as bigint)
0067                         set @i = @i + 1                                
0068                 end 
0069         ---------------------------------------------- 
0070         return @result                                
0071 end  
0072 GO
0073  
0074 ---- 调用示例 
0075 select dbo.maker_fInt2Hex(19912939123,16)
0076 select dbo.maker_fHex2Int('7FFFFFFFFFFFFFFF')
0077 GO
0078 -- 结果 
0079 /* 
0080  00000004A2E75673 
0081  
0082 （所影响的行数为 1 行） 
0083  
0084                       
0085 --------------------  
0086  9223372036854775807  
0087  
0088 （所影响的行数为 1 行） 
0089 */