IP Numeric To String

,

A user defined function that converts an integer value to an IP address in dot notation format. This is performed by promoting a 32 bit signed integer value to a signed 64 bit bigint and converted to a binary representation of the integer.

The purpose of this script is to allow the storage of an IP address which may be indexed without seperating into 4 tinyint columns.

/**
 * Returns a dot notation IP address such as XXX.XXX.XXX.XXX
 * From a 32 bit unsigned integer.
 * 
 * Author: Gary Feltham <gary.feltham@fpro.co.uk>
 * 
 * SQL Server does not use unsigned int values, the ip storage has
 * been promoted to a bigint (64 bit) value.
 */
CREATE FUNCTION dbo.ipNumericToString 
(
	@ip bigint
)
RETURNS varchar(15)
AS

BEGIN

DECLARE 
	@octetA binary(1), -- individual octets
	@octetB binary(1),
	@octetC binary(1),
	@octetD binary(1),
	@str varchar(15), -- the resulting string
	@b binary(4) -- the binary value

	SET @b = CAST(@ip AS binary(4))
	
	set @octetA = SUBSTRING(@b, 1, 1)
	set @octetB = SUBSTRING(@b, 2, 1)
	set @octetC = SUBSTRING(@b, 3, 1)
	set @octetD = SUBSTRING(@b, 4, 1)

	-- need to cast first to a tiny int then to a
string, casting immediately will
	-- set the byte value of a char not the numerics
	set @str = 
		CAST(CAST(@octetA AS tinyint) AS varchar(3)) + '.' +
		CAST(CAST(@octetB AS tinyint) AS varchar(3)) + '.' +
		CAST(CAST(@octetC AS tinyint) AS varchar(3)) + '.' +
		CAST(CAST(@octetD AS tinyint) AS varchar(3))

RETURN @str
END

Rate

Share

Share

Rate