Convert BigInt to IP address - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

Convert BigInt to IP address

Author

Topic

DLTaylor
Posting Yak Master

136 Posts

Posted - 2008-10-28 : 12:41:29

I have a colleague who has come to me with a SQL query …

In SQL 2005 there is a table with a field containing a BigInt string;

E.g. data ‘169090586’

…the BigInt data is supposed to contain an IP address – but when viewed in the table looks like;

‘169090586’ when it is should look like e.g. ’10.20.30.26’

My question is; can this data be converted back to show as an IP address?
(if indeed it ever was an IP address!!!)

This forum has been brilliant for me in the past – and I’m having a bit of a shot in the dark (sorry) hoping this makes some sense to someone.

Thanks

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-10-28 : 13:08:30

EDIT -- What I've written here is total guff. Please ignore.

I doubt so if it is as you described. It looks like you are loosing the position of the periods when you are importing the data.

If all the integers were 12 digits longs like this :

192168001001
192168010251
010010010001

Then it would be possible as every block of 3 digits is a part of the ip address.

Do you have any examples of your data and the valid corresponding ip address? We might be able to help you spot the pattern if there is a way.

-------------
Charlie

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-10-28 : 13:10:53

try this:
http://sqlblog.com/blogs/denis_gobo/archive/2008/10/05/9266.aspx

_______________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.1 out!

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-10-28 : 13:10:54

~~169090586 = 169.009.005.086?~~ :(

-------------
Charlie

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-10-28 : 13:13:32

[code]
CREATE FUNCTION dbo.IntegerToIPAddress (@IP AS bigint)

RETURNS varchar(15)

AS

BEGIN

DECLARE @Octet1 tinyint

DECLARE @Octet2 tinyint

DECLARE @Octet3 tinyint

DECLARE @Octet4 tinyint

DECLARE @RestOfIP bigint

SET @Octet1 = @IP / 16777216

SET @RestOfIP = @IP - (@Octet1 * 16777216)

SET @Octet2 = @RestOfIP / 65536

SET @RestOfIP = @RestOfIP - (@Octet2 * 65536)

SET @Octet3 = @RestOfIP / 256

SET @Octet4 = @RestOfIP - (@Octet3 * 256)

RETURN(CONVERT(varchar, @Octet1) + '.' +

CONVERT(varchar, @Octet2) + '.' +

CONVERT(varchar, @Octet3) + '.' +

CONVERT(varchar, @Octet4))

END
Go

SELECT dbo.IntegerToIPAddress(169090586)
[/code]

RETURNS : 10.20.30.26

-------------
Charlie

DLTaylor
Posting Yak Master

136 Posts

Posted - 2008-10-28 : 13:22:05

That is incredible
I think it will take me a while to work out exactly what is going on but i will credit your help.
Thanks

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-10-28 : 13:47:20

Another way:


select
	IPBIG,
	IP_String =
	convert(varchar(3),convert(tinyint,substring(IPBin,1,1)))+'.'+
	convert(varchar(3),convert(tinyint,substring(IPBin,2,1)))+'.'+
	convert(varchar(3),convert(tinyint,substring(IPBin,3,1)))+'.'+
	convert(varchar(3),convert(tinyint,substring(IPBin,4,1)))
from
	(
	select
		IPBIG,
		IPBin =convert(varbinary(4),ipbig)
	from
		(-- Test data
		select IPBIG=convert(bigint,169090586)
		) aa
	) a


Results:
IPBIG                IP_String       
-------------------- --------------- 
169090586            10.20.30.26

(1 row(s) affected)

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources