How to convert IP address stored in a table into hexadecimal? the output I am expecting is 00:00:8D:69:DF:B6 for an IP 141.105.223.182

Question

I used this for converting a IP address, (I read this from web not my SQL). I now want to understand how can I convert an IP address stored in a column to HEXADECIMAL.

DECLARE @IP varchar(30) 
SET @IP = '192.168.1.1'

SELECT 

'00:00:'+
RIGHT(CONVERT(varchar(max), CONVERT(VARBINARY(4), CAST(PARSENAME(@IP, 4) AS int)), 2),2) + ':'
+
RIGHT(CONVERT(varchar(max), CONVERT(VARBINARY(4), CAST(PARSENAME(@IP, 3) AS int)), 2),2) + ':'
+
RIGHT(CONVERT(varchar(max), CONVERT(VARBINARY(4), CAST(PARSENAME(@IP, 2) AS int)), 2),2) + ':'
+
RIGHT(CONVERT(varchar(max), CONVERT(VARBINARY(4), CAST(PARSENAME(@IP, 1) AS int)), 2),2) 

Answer 1

Your almost there already!

If I understand your requirements correctly, you want to replace @IP with a table's column data

You just need to add a from and change the selection criteria

I create a temp table #testdata to demonstrate, you just need to select FROM your table

create table #testdata
([IP] varchar(25))

insert into #testdata
values
('192.168.1.1')
insert into #testdata
values
('192.168.1.2')
insert into #testdata
values
('192.168.1.3')

SELECT 
'00:00:'+
RIGHT(CONVERT(varchar(max), CONVERT(VARBINARY(4), CAST(PARSENAME([IP], 4) AS int)), 2),2) + ':'
+ RIGHT(CONVERT(varchar(max), CONVERT(VARBINARY(4), CAST(PARSENAME([IP], 3) AS int)), 2),2) + ':'
+ RIGHT(CONVERT(varchar(max), CONVERT(VARBINARY(4), CAST(PARSENAME([IP], 2) AS int)), 2),2) + ':'
+ RIGHT(CONVERT(varchar(max), CONVERT(VARBINARY(4), CAST(PARSENAME([IP], 1) AS int)), 2),2) 
FROM #testdata