My previous conversion post covered unix epoch date conversion. Here I discuss my adventures with converting decimal ip addresses.

Internet IP v 4 addresses look like this 41.22.154.123. 4 numbers separated by periods. Each number ranges from 0 to 255. I never knew it, but these addresses are commonly stored as binary or as decimal numbers. One reason is to facilitate sorting the addresses. There’s no good way to sort the addresses in the dotted notation.

Here’s the function I ended up using with Oracle PL/SQL to convert the decimal value to dotted.

create or replacefunction inttoip(ip_address integer) return varchar2
deterministic
is
begin    
return to_char(mod(trunc(ip_address/256/256/256),256)) ||'.'||to_char(mod(trunc(ip_address/256/256),256))           ||'.'||to_char(mod(trunc(ip_address/256),256))           ||'.'||to_char(mod(ip_address,256));
end;

A colleague sent to me some SQL Server and Oracle conversions using a slightly different approach. I’ve copied these below. And of course, google searches will quickly direct you to other material in this area.

-- MS SQL Server with convert to bigint
SELECT dbo.IPADDRESS.IPADDRESS,
CAST(ROUND( (cast(dbo.IPADDRESS.IPADDRESS as bigint) / 16777216 ), 0, 1) AS varchar(4)) + '.' +
CAST((ROUND( (cast(dbo.IPADDRESS.IPADDRESS as bigint) / 65536 ), 0, 1) % 256) AS varchar(4)) + '.' +
CAST((ROUND( (cast(dbo.IPADDRESS.IPADDRESS as bigint) / 256 ), 0, 1) % 256) AS varchar(4)) + '.' + 
CAST((cast(dbo.IPADDRESS.IPADDRESS as bigint) % 256 ) AS varchar(4)) as IPDottedNotation
FROM  dbo.IPADDRESS

-- MS SQL Server From non numeric datatype
 
SELECT     dbo.DEVICE.HOSTNAME, dbo.IPADDRESS.IPADDRESS,
CAST( ROUND( (dbo.IPADDRESS.IPADDRESS / 16777216 ), 0, 1) AS varchar(4)) + '.' +
CAST( (ROUND( (dbo.IPADDRESS.IPADDRESS / 65536 ), 0, 1) % 256) AS varchar(4)) + '.' +
CAST( (ROUND( (dbo.IPADDRESS.IPADDRESS / 256 ), 0, 1) % 256) AS varchar(4)) + '.' + 
CAST( (dbo.IPADDRESS.IPADDRESS % 256 ) AS varchar(4)) as IPDottedNotation
FROM dbo.IPADDRESS 

-- Oracle PL/SQL
SELECT DEVICE.HOSTNAME, IPADDRESS.IPADDRESS,
cast(
CAST(ROUND((IPADDRESS.IPADDRESS / 16777215 ), 0) AS varchar2(3)) || '.' ||
CAST(MOD(ROUND((IPADDRESS.IPADDRESS / 65535), 0), 256) AS varchar2(3)) || '.' ||
CAST(MOD(ROUND((IPADDRESS.IPADDRESS / 256  ), 0), 256) AS varchar2(3)) || '.' ||
CAST(MOD(IPADDRESS.IPADDRESS, 256 ) AS varchar2(3))
as char(20)) as IPDottedNotation
FROM IPADDRESS
 
Advertisements