SQL Server Performance Forum – Threads Archive

IP to Long conversion

Does anyone have an IP to Long and Long to IP convertion script or UDF? GA

consider this: SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO ALTER FUNCTION fn_IPtoNUM(@ip varchar(15))
RETURNS numeric(12,0)
AS
BEGIN
declare @ipstr varchar(50),@leftindex int,@stuffindex int,@dotindex int,@iptonum varchar(15) –select dbo.fn_iptonum(‘10.5.252.255’) select @dotindex=charindex(‘.’,@ip,1), @iptonum=left(@ip,@dotindex-1), @ip=stuff(@ip,1,@dotindex,”) while len(@ip)>0
begin
set @dotindex=charindex(‘.’,@ip,1)
if @dotindex=0
begin
select @leftindex=len(@ip), @stuffindex=len(@ip)
end
else
begin
select @[email protected], @[email protected]
end
select @ipstr=left(@ip,@leftindex)
set @ipstr=case len(@ipstr) when 1 then ’00’[email protected] when 2 then ‘0’[email protected] else @ipstr end
select @ip=stuff(@ip,1,@stuffindex,”), @[email protected][email protected]
end
RETURN(@iptonum)
END GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Is this you meant?
declare @ip varchar(20)
set @ip=’10.5.252.255′ select parsename(@ip,4)+replicate(‘0’,3-len(parsename(@ip,4)))+parsename(@ip,3)+
parsename(@ip,2)+replicate(‘0’,3-len(parsename(@ip,2)))+parsename(@ip,1)+replicate(‘0’,3-len(parsename(@ip,1)))
as Long
Madhivanan Failing to plan is Planning to fail

Nice one, Madhivanan! I forgot all about PARSENAME … Just one thing, I wonder what Gagiovlasitis meant by ‘LONG’ – is it ‘010.005.252.255’ for ‘10.5.252.255’, or does he mean a numeric equivalent – as in the ‘long integer’ data type? If it is the format with leading zeroes, then Madhivanan’s solution needs a few minor adjustments: IP > LONG
select replicate(‘0’,3-len(parsename(@ip,4))) + parsename(@ip,4)
+ ‘.’ + replicate(‘0’,3-len(parsename(@ip,3))) + parsename(@ip,3)
+ ‘.’ + replicate(‘0’,3-len(parsename(@ip,2))) + parsename(@ip,2)
+ ‘.’ + replicate(‘0’,3-len(parsename(@ip,1))) + parsename(@ip,1)
as Long LONG > IP
select CAST(CAST(parsename(@ip,4) AS SMALLINT) AS VARCHAR(3))
+ ‘.’ + CAST(CAST(parsename(@ip,3) AS SMALLINT) AS VARCHAR(3))
+ ‘.’ + CAST(CAST(parsename(@ip,2) AS SMALLINT) AS VARCHAR(3))
+ ‘.’ + CAST(CAST(parsename(@ip,1) AS SMALLINT) AS VARCHAR(3))

IP > LONG can also be a bit simpler: select @ip =
RIGHT(’00’ + parsename(@ip,4), 3)
+ ‘.’ + RIGHT(’00’ + parsename(@ip,3), 3)
+ ‘.’ + RIGHT(’00’ + parsename(@ip,2), 3)
+ ‘.’ + RIGHT(’00’ + parsename(@ip,1), 3)

Thats cool Adriaan [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] Madhivanan Failing to plan is Planning to fail

Wouldn’t the value of IP in the case from LONG to IP look like set @ip=’105252255′ instead of set @ip=’10.5.252.255′ ? If so the conversion is far more tricky than one might think. Anyway, I would think, that such conversion usually are done because some sorting issues when using a VARCHAR column. If this is here also the case, here are some ways to get a correct sort of I adresses when using a VARCHAR column. <pre id="code"> create table T ( ipAdd varchar(15) ) insert into T select rtrim(abs(binary_checksum(newid()))%256) + ‘.' + rtrim(abs(binary_checksum(newid()))%256) + ‘.' + rtrim(abs(binary_checksum(newid()))%256) + ‘.' + rtrim(abs(binary_checksum(newid()))%256) from Northwind..Orders go – sort by ipAdd select ipAdd from T order by 0 + parsename(ipAdd,4), 0 + parsename(ipAdd,3), 0 + parsename(ipAdd,2), 0 + parsename(ipAdd,1) go </pre id="code"> or <pre id="code"> CREATE VIEW Patterns AS SELECT REPLICATE(‘_’,N1.n) + ‘.' + REPLICATE(‘_’,N2.n) + ‘.' + REPLICATE(‘_’,N3.n) + ‘.' + REPLICATE(‘_’,N4.n) AS pattern, 1 AS s1, N1.n AS l1, N1.n+2 AS s2, N2.n AS l2, N1.n+N2.n+3 AS s3, N3.n AS l3, N1.n+N2.n+N3.n+4 AS s4, N4.n AS l4 FROM Nums AS N1, Nums AS N2, Nums AS N3, Nums AS N4 WHERE N1.n <= 3 AND N2.n <= 3 AND N3.n <= 3 AND N4.n <= 3 GO SELECT T.* FROM T JOIN Patterns ON ipAdd LIKE pattern ORDER BY CAST(SUBSTRING(ipAdd,s1,l1) AS TINYINT), CAST(SUBSTRING(ipAdd,s2,l2) AS TINYINT), CAST(SUBSTRING(ipAdd,s3,l3) AS TINYINT), CAST(SUBSTRING(ipAdd,s4,l4) AS TINYINT) GO </pre id="code"> You also need a numerical helper table to get this running. Lastly <pre id="code"> create function ipVal ( @ipAdd varchar(15) ) returns bigint as begin declare @a varchar(3), @b varchar(3), @c varchar(3), @d varchar(3) set @ipAdd = replace(@ipAdd,’.’,space(1)) exec master..xp_sscanf @ipAdd, ‘%s%s%s%s’, @a output, @b output, @c output, @d output return 256.*(256.*(256.*@a + @b)+ @c) + @d end GO </pre id="code"> This is the combined input by Steve Kass and Itzik Ben-Gan from a very interesting thread on the MS newsgroups. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] – Frank Kalis Microsoft SQL Server MVP http://www.insidesql.de</a> Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a>

Actually, I was looking for something equivilant to mySQL’s INET_ATON function. I was playing around with it and came up with this: —
declare @ip varchar(15)
set @ip = ‘209.207.224.40’ [email protected],
CONVERT(BIGINT,PARSENAME(@ip,4)) * POWER(256,3)
+ CONVERT(BIGINT,PARSENAME(@ip,3)) * POWER(256,2)
+ CONVERT(BIGINT,PARSENAME(@ip,2)) * 256
+ CONVERT(BIGINT,PARSENAME(@ip,1)) –Results
–209.207.224.403520061480

Hi Madhivanan,
Good post especially pointing on PARSENAME.
After reading this i used your logic to implement the same in my project and changes made as Adriaan said.
I was doing this thing in my first function which i posted. input:10.234.210.52
Output:10234210052 another eg:
i/p: 172.2.2.2
o/p: 172002002002
But yours is far much simpler because of Parsename which i dint knew about.
Thanx alot.

]]>