Convert IP to a comparable numeric value

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"

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

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"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources