Background

Convert Hexadecimal number ( base 16) to Decimal number ( base 16)

Code

Here are the steps for converting an Hexadecimal number to Integer

1. Convert any occurence of char (a-f) to its decimal equivalent
• a -> 10
• b -> 11
• c -> 12
• d -> 13
• e -> 14
• f -> 15
2. Count number of Characters
3. Starting from left most character navigate to right most character
• Multiplier
• Will be length of hex number
• Assign length as place-holder-1
• Get character as place-holder-2
• place-holder-3 = ( place-holder-2 ) * ( 16 raised to ( place-holder-1  – 1  ) )
• Accumulate place-holder-3

SQL

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

if object_id('[dbo].[itvf_hexToDec]') is null
begin

exec('CREATE FUNCTION [dbo].[itvf_hexToDec]
(
@number varchar(60)
)
RETURNS @itvfResult TABLE
(
[hex] varchar(60)
, [dec]  bigint
)
AS
BEGIN

return

END
'
)

end
go

ALTER FUNCTION [dbo].[itvf_hexToDec]
(
@number varchar(60)
)
RETURNS @itvfResult TABLE
(
[hex] varchar(60)
, [dec]  bigint
)
AS
BEGIN

-- =============================================
-- Author:		Daniel Adeniji
-- Create date: 2019-03-03
-- Description:	Converts Number in Hex Based to Dec Base
-- =============================================

declare @id     smallint
declare @len    smallint
declare @nth    smallint

declare @rc      int
declare @result  bigint

declare @ch      char(1)
declare @chAsInt int

declare @CHAR_ASCII_a tinyint

set @CHAR_ASCII_a = 97

-- Convert to lower case
set @number = lower(@number)

-- Strip out leading 0x if present
if left(@number,2) = '0x'
begin

set @number = substring(@number, 3, 255)

end

-- Get Length
set @len= len(@number)

set @id = 1
set @nth = @len
set @result = 0

/*
Transverse number one character at a time
*/
while (@id = '0' ) and (@ch = 'a' ) and (@ch <= 'f') )
begin

set @chAsInt = ascii(@ch)

set @chAsInt = 10 + @chAsInt - @CHAR_ASCII_a

end

-- raise current number to 16 raised to position in string
set @rc = @chAsInt * power(16, ( @nth -1 ) )

-- accumulate number
set @result = @result + @rc

-- move pointer
set @id = @id + 1

-- adjust nth power
set @nth = @nth - 1

end

insert into @itvfResult
(
[hex]
, [dec]
)
select
@number
, @result

return

END
GO

grant select on [dbo].[itvf_hexToDec] to [public]
go

Sample

declare @number varchar(60);

set @number = 'A'
set @number = '89'

set @number = '190'

set @number = '2710'

select *

from   [dbo].[itvf_hexToDec](@number)

Source Code Control

Github

1. Function
• Scaler
• dbo.fn_hexToDec