Convert decimal/dotted IP number to hex string

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)

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s