Categories
Programming SQL / Database

SQL/Server function to format integer IP address

The following is a small SQL/Server function to convert an IP address stored as an integer (actually bigint) into a human-readable string in the format ‘1.2.3.4’. I’m using it as part of a geolocation by IP address project.

CREATE FUNCTION IP2String (@IPAddress bigint)
RETURNS varchar(15)
AS
BEGIN
DECLARE @IP1 bigint
DECLARE @IP2 bigint
DECLARE @IP3 bigint
DECLARE @IP4 bigint
DECLARE @ResultVar varchar(15)

SET @IP1 = @IPAddress / 16777216
SET @IPAddress = @IPAddress – @IP1 * 16777216
SET @IP2 = @IPAddress / 65536
SET @IPAddress = @IPAddress – @IP2 * 65536
SET @IP3 = @IPAddress / 256
SET @IPAddress = @IPAddress – @IP3 * 256
SET @IP4 = @IPAddress

SET @ResultVar = CAST(@IP1 AS varchar(3)) + ‘.’
+ CAST(@IP2 AS varchar(3)) + ‘.’
+ CAST(@IP3 AS varchar(3)) + ‘.’
+ CAST(@IP4 AS varchar(3))
RETURN @ResultVar

END