Mark-101232 (7/24/2009)
Jeff Moden (7/24/2009)
Mark (7/13/2009)
I think both functions can be simplified
create function dbo.ConvertIp2Num(@ip nvarchar(15))returns bigint
as
begin
return ((cast(parsename(@ip,4) as bigint)*256+
cast(parsename(@ip,3) as bigint))*256+
cast(parsename(@ip,2) as bigint))*256+
cast(parsename(@ip,1) as bigint)
end
That won't return the correct answer... the octets must be multiplied by powers of 256.
select dbo.ConvertIp2Num('192.15.10.125') -- gives 3222211197
The nesting of the multiplys gives the effect of multiplying by powers of 256. (Look at the brackets)
Surprisingly, the function above seemed to take longer on average than the function below (which is what was shown earlier):
CREATE FUNCTION [dbo].[fnDot2LongIP]
(@ipaddr varchar(15))
RETURNS bigint
AS
BEGIN
DECLARE @longip bigint
SELECT @longip = CAST(PARSENAME(@ipaddr, 1) AS bigint) % 256
+ CAST(PARSENAME(@ipaddr, 2) AS bigint) % 256 * 256
+ CAST(PARSENAME(@ipaddr, 3) AS bigint) % 256 * 65536
+ CAST(PARSENAME(@ipaddr, 4) AS bigint) % 256 * 16777216
RETURN @longip
END
As for the modulus operations, I tried the function with and without them. It seemed slightly quicker WITH them rather than without them. Does anyone else have the same results?