I had need today to pull a bunch of IP numbers from a database and return them as hex strings from a query to my code. It took a little messing, but finally figured out a solution.

DECLARE @b varchar(30) SET @b = '64.233.160.0'

SELECT 
RIGHT(CONVERT(varchar(max), CONVERT(VARBINARY(4), CAST(PARSENAME(@b, 4) AS int)), 2),2)
+
RIGHT(CONVERT(varchar(max), CONVERT(VARBINARY(4), CAST(PARSENAME(@b, 3) AS int)), 2),2)
+
RIGHT(CONVERT(varchar(max), CONVERT(VARBINARY(4), CAST(PARSENAME(@b, 2) AS int)), 2),2)
+
RIGHT(CONVERT(varchar(max), CONVERT(VARBINARY(4), CAST(PARSENAME(@b, 1) AS int)), 2),2)

The above returns ’40E9A000′. Breaking it down:

— gives me the first octet as an integer

CAST(PARSENAME(@b, 4) AS int)

— converts the integer to a hex literal

CONVERT(VARBINARY(4), CAST(PARSENAME(@b, 4) AS int))

–converts the hex literal to a string

CONVERT(varchar(max), CONVERT(VARBINARY(4), CAST(PARSENAME(@b, 4) AS int)), 2)

–grabs the right hand 2 characters of the hex string

RIGHT(CONVERT(varchar(max), CONVERT(VARBINARY(4), CAST(PARSENAME(@b, 4) AS int)), 2),2)