0

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)
Hari Hemachandran
  • 3
  • 3
  • Please specify database engine you're using in tags – Vadim Kotov Aug 10 '18 at 10:29
  • I am using SQL server management studio 2017 – Hari Hemachandran Aug 10 '18 at 10:42
  • As I understand your question you want to know how to convert a table column (instead of a variable)? Answer: replace `@IP` with the name of your column and add `FROM` – Alex Aug 10 '18 at 12:53
  • Can you explain what doesn't work with your code? The desired output is for an IP of 141.105.223.182 but in your example you have a different IP address. If you change your variable to the same IP as in the title of your question you get the EXACT output you claim you want. – Sean Lange Aug 10 '18 at 13:59

1 Answers1

0

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
freshAir
  • 26
  • 2