dbWriter SQL function find long number from IP
A numerical representation of IP address.
Basically the math for IP 1.2.3.4 is:
1.2.3.4 = 4 + (3 * 256) + (2 * 256 * 256) + (1 * 256 * 256 * 256)
This function returns the Long IP or a negative number if there has
been an error
-1 is an incorrect formatted IP... ERROR
-2 the IP entered was not numeric... ERROR
CREATE FUNCTION dbo.LONGfromIP (
@ip VARCHAR(15)
)
RETURNS BIGINT
AS
BEGIN
DECLARE @rv BIGINT,
@o1 BIGINT,
@o2 BIGINT,
@o3 BIGINT,
@o4 BIGINT
IF
(ISNUMERIC(PARSENAME(@ip, 4)) = 1) AND
(ISNUMERIC(PARSENAME(@ip, 3)) = 1) AND
(ISNUMERIC(PARSENAME(@ip, 2)) = 1) AND
(ISNUMERIC(PARSENAME(@ip, 1)) = 1)
BEGIN
SELECT
@o1 = CONVERT(BIGINT, PARSENAME(@ip, 4)),
@o2 = CONVERT(BIGINT, PARSENAME(@ip, 3)),
@o3 = CONVERT(BIGINT, PARSENAME(@ip, 2)),
@o4 = CONVERT(BIGINT, PARSENAME(@ip, 1))
IF (@o1 BETWEEN 0 AND 255)
AND (@o2 BETWEEN 0 AND 255)
AND (@o3 BETWEEN 0 AND 255)
AND (@o4 BETWEEN 0 AND 255)
BEGIN
SET @rv = (@o1 * 16777216) +
(@o2 * 65536) +
(@o3 * 256) +
(@o4)
END
ELSE
SET @rv = -1 -- bad ip ERROR
END
ELSE
SET @rv = -2 -- not numeric ERROR
RETURN @rv |
|
