sql 自定义函数-16进制转10进制

做过笔记，好记性不如烂笔头：

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[HEXTOINT]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[HEXTOINT]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO



CREATE  function HEXTOINT(@hexstr varchar(10))
returns bigint
as
begin
if left(@hexstr,2) in ('0x','0X') set @hexstr=substring(@hexstr,3,10)
declare @i int, @res bigint, @l int, @c char, @ascii0 int, @asciiF int
select @i=1, @l=len(@hexstr), @res=0, @ascii0=ascii('0'), @asciiF=ascii('F')
if @hexstr is null OR @l=0 return null
while @i<=@l begin
   set @c=upper(substring(@hexstr,@i,1))
   if not ascii(@c) between @ascii0 and @asciiF return(null)
   set @res=@res+cast(1.0 as bigint)*case when isnumeric(@c)=1 then 
cast(@c as int) else ascii(@c)-55 end*power(16,@l-@i) 
   set @i=@i+1
end
return(@res)
end




GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO