Friday, June 8, 2007

dotted notation to number

Writing my ip address management software I needed something to order my ips in sql.The dotted notation wasn't so efficient, so, I needed to convert it in an integer.
Wandering on the Net, and with the CCNA study guide open, here's the result, starting from a script read somewhere...


CREATE FUNCTION dbo.IPDottedToNumber( @IPAddress varchar(15))
RETURNS bigint
AS
BEGIN
DECLARE
@biOctetA bigint,
@biOctetB bigint,
@biOctetC bigint,
@biOctetD bigint,
@biIP bigint

DECLARE @tbl TABLE

(
OctetNo smallint,
Octet bigint
)

INSERT INTO @tbl
SELECT ElementID, CONVERT(bigint,Element) FROM dbo.Split(@IPAddress, '.')

IF (SELECT COUNT(*) FROM @tblArray WHERE Octet BETWEEN 0 AND 255) = 4
BEGIN

SET @biOctetA = (SELECT (Octet * 256 * 256 * 256) FROM @tblArray WHERE OctetNo = 1)

SET
@biOctetB = (SELECT (Octet * 256 * 256 ) FROM @tblArray WHERE
OctetNo = 2)

SET
@biOctetC = (SELECT (Octet * 256 ) FROM @tblArray WHERE
OctetNo = 3)

SET
@biOctetD = (SELECT (Octet) FROM @tblArray WHERE
OctetNo = 4)

SET
@biIP = @biOctetA + @biOctetB + @biOctetC + @biOctetD


END


RETURN(@biIP)
END

No comments: