#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2008
    Posts
    3
    Rep Power
    0

    Converting hex varchar to decimal bigint


    Hi there,

    I'm trying to convert the varchar MAC address '0000123ff2a5' (hex) into it's decimal representation as a bigint.

    I've tried several T-SQL functions but none of them have successfully converted the value. The result should be '0000306180773158'. So, what I'm trying to do is convert a MAC address (hex) into the equipment address (decimal).

    Any ideas?

    Thanks so much,
    Kristina
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2007
    Posts
    168
    Rep Power
    7
    Code:
    create function hex2int(@s varchar(16)) --Convert hex to 
    returns bigint -- e.g. select dbo.hex2int('7ff2a5')
    as begin
    select @s=upper(@s)
    declare @i int, @len int, @c char(1), @result bigint
    select @len=len(@s), @i=@len, @result=case when @len>0 then 0 end
    while (@i>0)
    begin
      select @c=substring(@s,@i,1), @result=@result
      +(ASCII(@c)
        -(case when @c between 'A' and 'F' then 55 
          else case when @c between '0' and '9' then 48 end
          end))
      *power(16.,@len-@i)
      ,@i=@i-1
    end -- while
    return @result
    end -- function

IMN logo majestic logo threadwatch logo seochat tools logo