If you have seen DHCP lease pools lately you may have come across unique IDs that look nothing like a MAC addresses you are used to see.
Below a sample of what you may find. Taking a closer look you may recognize 48 character long hexadecimal strings.
So now what?
Thankfully Microsoft SQL Server can easily help you make sense out of the hexadecimal string.
The converted alphanumerical representation of the above depicted hexadecimal string looks like this:
which in return looks very much like it includes a mac address 00:17:59:10:F1:40 – potentially something you can make a reservation for in your DHCP scope.
Now the question – How did I get from the long hexadecimal string to the alphanumeric outcome?
Easy enough using transact SQL.
In the code listed below 2 temporary tables are used to help translate the long unique IDs I obtained from the DHCP server into understandable output
Create table #HexToAlpha
, alphastring varchar(24)
Insert into #HexToAlpha values(‘53617373616E’, ”)
Insert into #HexToAlpha values( ‘636973636f2d303031372e353931302e663134302d566c31’,”)
Insert into #HexToAlpha values( ‘yourhexadeximalstring’,”)
create table #NHC (numbr smallint identity(0,1), hex char(2), chr varchar(1))
insert into #NHC (chr) select top 256 null from sysobjects
declare @hexes char(16)
set @hexes = ‘0123456789ABCDEF’
update #NHC set hex = substring(@hexes,numbr/16+1,1)+ substring(@hexes, numbr– (numbr/16 * 16)+1, 1), chr=char(numbr)
while exists (select * from #hextoalpha where len(hexstring) <> len(alphastring)*2)
update #hextoalpha set alphastring= alphastring +
(select chr from #NHC where substring(hexstring, len(alphastring)*2 +1,2) = hex )
where len(hexstring) <> len(alphastring)*2
–select * from #NHC – in case you want to see what the hex lookup table contains.
drop table #NHC
select * from #hextoalpha
drop table #hextoalpha
Hopefully you’ll find this Segway between SQL Server and Windows infrastructure helpful.