Convert MAC address from hex to 16 integar value

• rohanverma3

  Old Hand

  Points: 323

  More actions

  December 27, 2012 at 8:21 pm

  #393848

  Hello,

  I have a database of MAC addresses (Called DB-A) that I need to join to another db (Called DB-B), the issue is first DB (DB-A) has MAC addresses in hex format (00:00:00:42:3b:c6) while the MAC addresses in second db (DB-B) is in a 16 character long format (0000051277056192).

  Could someone please help me with a SQL script that I can use to convert the MAC from hex to the 16 digit value?

  E.g.

  00:00:00:42:3b:c6 --> 0000004340678203

  Thanks

• Orlando Colamatteo

  SSC Guru

  Points: 182293

  More actions

  December 28, 2012 at 12:40 pm

  #1572204

  What have you tried so far?

  There are no special teachers of virtue, because virtue is taught by the whole community.
  --Plato

• David Webb-CDS

  SSCoach

  Points: 17398

  More actions

  December 28, 2012 at 1:45 pm

  #1572232

  Sorry, but I'm very dense today apparently. What are the rules for converting one to the other?

  ---

  And then again, I might be wrong ...
  David Webb

• David Webb-CDS

  SSCoach

  Points: 17398

  More actions

  December 28, 2012 at 2:43 pm

  #1572250

  Your milage may vary, but this is one way to go from the integer value to the hex with colons. This was taken from a forum post by Paul White, to whom all the credit should be given.

  declare @xint bigint

  set @xint = 4340678

  declare @hex varbinary(20)

  set @hex = CAST(@xint as varbinary(20)) -- converts the integer to its hex values

  declare @bstring varchar(20)

  select @hex

  -- puts the hex values into a string

  select @bstring = (SELECT --@hex AS OriginalValue,

  CAST('' AS XML).value('xs:hexBinary(sql:variable("@hex"))', 'VARCHAR(MAX)') AS ConvertedString)

  -- stuff the colons in at the correct places

  select stuff(stuff(stuff(stuff(stuff(stuff(stuff(@bstring,3,0,':'),6,0,':'),9,0,':'),12,0,':'),15,0,':'),18,0,':'),21,0,':')

  ---

  And then again, I might be wrong ...
  David Webb