Convert Hex String to IP Address SQL

Hi,

I came across this code theu MSDN where someone asked how to convert Hex String to IP Address. Its work sharing so here it is.

DECLARE @v1 AS char(8);
SET @v1 = '0A370D16' ;
DECLARE @b1 AS binary;
DECLARE @b2 AS binary;
DECLARE @b3 AS binary;
DECLARE @b4 AS binary;

SELECT @b1 = sys.fn_cdc_hexstrtobin(SUBSTRING(@v1,1,2));
SELECT @b2 = sys.fn_cdc_hexstrtobin(SUBSTRING(@v1,3,2));
SELECT @b3 = sys.fn_cdc_hexstrtobin(SUBSTRING(@v1,5,2));
SELECT @b4 = sys.fn_cdc_hexstrtobin(SUBSTRING(@v1,7,2));
SELECT @b1, @b2, @b3, @b4;

DECLARE @d1 AS int;
DECLARE @d2 AS int;
DECLARE @d3 AS int;
DECLARE @d4 AS int;

SELECT @d1 = CAST (@b1 AS int);
SELECT @d2 = CAST (@b2 AS int);
SELECT @d3 = CAST (@b3 AS int);
SELECT @d4 = CAST (@b4 AS int);
SELECT @d1, @d2, @d3, @d4;
SELECT CAST (@d1 AS char(2)) + '.' + CAST (@d2 AS char(2)) +'.'+ CAST (@d3 AS char(2)) + '.'+ CAST (@d4 AS char(2)) AS IPAddress

Output Will Be
******************


---- ---- ---- ----
0x0A 0x37 0x0D 0x16

(1 row(s) affected)


----------- ----------- ----------- -----------
10 55 13 22

(1 row(s) affected)

IPAddress
-----------
10.55.13.22

(1 row(s) affected)