Transact SQL – Convert Hexadecimal number to Decimal

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
        Link
    • Multi-Statement Value Function
      • dbo.itvf_hexToDec
        Link

 

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s