December 27, 2012 at 8:21 pm
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
December 28, 2012 at 12:40 pm
What have you tried so far?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
December 28, 2012 at 1:45 pm
Sorry, but I'm very dense today apparently. What are the rules for converting one to the other?
December 28, 2012 at 2:43 pm
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,':')
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply