MSSQL 16진수를 10진수로 변환하기

출처  http://www.devpia.com/MAEUL/Contents/Detail.aspx?BoardID=38&MAEULNo=16&no=37693&ref=37693


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].HexToDec') and OBJECTPROPERTY(id, N'IsScalarFunction') = 1)
drop function [dbo].HexToDec
GO

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
/*-------------------------------------------------------------------------
 HexToDec
 목  적 : Hex 스트링을 십진수로 변환
 EX)
  declare @result_val int
  set @result_val = dbo.HexToDec ('FFFF')
  select @result_val
*/-------------------------------------------------------------------------
CREATE FUNCTION HexToDec  (@HexStr varchar(1024))
RETURNS int
AS
Begin
declare @len int
declare @i int
declare @weight int
declare @dec int

set @len = len(@HexStr)
set @i = 1
set @weight = 1
set @dec = 0

while @i <= @len
begin
 set @dec = @dec + case UPPER(substring(@HexStr, @len-@i+1, 1))
        when 'A' THEN 10
        when 'B' THEN 11
        when 'C' THEN 12
        when 'D' THEN 13
        when 'E' THEN 14
        when 'F' THEN 15
         ELSE CONVERT(INT, UPPER(substring(@HexStr, @len-@i+1, 1)))
 end * @weight  
 set @weight = @weight * 16
 set @i = @i + 1
end

return @dec 
End
GO