Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 Script Library
 Convert IP to a comparable numeric value

Author  Topic 

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-03-01 : 08:18:08
I couldn't find this function in the script library so I thought I'd add it. Very useful when comparing IP addresses and checking if an IP is within a certain range, etc. The technique is known, this is just a sql server implementation
CREATE FUNCTION dbo.ip2num (
@ip varchar(15)
)
RETURNS bigint
AS

BEGIN

DECLARE @num bigint

SET @num =
256 * 256 * 256 * CAST(PARSENAME(@ip, 4) AS bigint) +
256 * 256 * CAST(PARSENAME(@ip, 3) AS bigint) +
256 * CAST(PARSENAME(@ip, 2) AS bigint) +
CAST(PARSENAME(@ip, 1) AS bigint)

RETURN @num

END


- Lumbago
If the facts don't fit the theory, change the facts. Albert Einstein

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-03-01 : 08:49:43
One CAST is enough,
CREATE FUNCTION dbo.ip2num
(
@ip varchar(15)
)
RETURNS bigint
AS

BEGIN

return (
select cast(16777216 as bigint) * PARSENAME(@ip, 4)
+ 65536 * PARSENAME(@ip, 3)
+ 256 * PARSENAME(@ip, 2)
+ PARSENAME(@ip, 1)
)
END



N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-03-01 : 09:04:05
I keep switching back and forth with my opinion about implicit conversion being good or bad, but I guess this one is hard to mess up so then I'm leaning slightly towards good as of now :)

- Lumbago
If the facts don't fit the theory, change the facts. Albert Einstein
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-03-01 : 11:26:54
I think there will be a difference in speed too, and not only to the cleaner code.
The difference would be the different methods (select vs set).


N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page
   

- Advertisement -