function to convert ip/mac address to integer and vise versa
-
Hello,
Just wondering if anyone has function for converting IPv4 address and Mac address to integer? and vise versa? Thanks.
-
My first question would be - WHY?????? Binary perhaps, but decimal?
MAC addresses CAN't be converted to integer (Int). Int is too small of a data type for that (MAC is a hexadecimal number representing something in the size of 2^48, int tops out around 2^31).
Also - an IP address is not ONE number - it's four numbers - did you plan on combining them in some way before converting
As for conversion programs from Hex to Decimal- you'll want to use CLR integration for that. Use this as a basis:
http://www.devx.com/vb2themax/Tip/19416?type=kbArticle&trk=MSCP
(that's the VB version of it)
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again? -
Many IP-range lookup type databases (such as tying IP to country) convert IPs into long decimal numbers and then take the passed decimal and do a BETWEEN with two other converted IPs, which allows for them to handle various ranges without worrying about whether it's a Class A, B, C network, or some subset of the above. Typically, the decimal is created in a manner like:
205.152.61.94
(205 * 256 * 256 * 256) + (152 * 256 * 256) + (61 * 256) + (94 * 1)
If that's what you need, there is a neat little trick that avoids having to do pattern matching on the periods, etc.
Use the built-in parsename function. It wasn't intended for this purpose, but works like a charm.
Assuming you toss the above address into a variable named @IP...
Parsename(@IP,4) returns 205
Parsename(@IP,3) returns 152
Parsename(@IP,2) returns 61
Parsename(@IP,1) returns 94
I hope that helps you down the path.
-
SELECT ID -- integer identity field
FROM dbo.IP_Adresses
WHERE IP_Address = @IP_Address
_____________
Code for TallyGenerator -
I think you're asking for trouble by converting to decimal... but it's your data...
You can build a couple of functions from the following example...
DECLARE @IPAddress VARCHAR(15) SET @IPAddress = '5.152.61.94' DECLARE @IPDecimal BIGINT SET @IPDecimal = 5152061094
--===== Convert IP to "decimal" SELECT CAST( REPLACE( STR(PARSENAME(@IPAddress,4),3) + STR(PARSENAME(@IPAddress,3),3) + STR(PARSENAME(@IPAddress,2),3) + STR(PARSENAME(@IPAddress,1),3) ,' ',0) AS BIGINT)
--===== Convert "decimal" to IP SELECT LTRIM( REPLACE( REPLACE( STUFF( STUFF( STUFF( STR( @IPDecimal ,12) ,10,0,'.') ,7,0,'.') ,4,0,'.') ,'.0','.') ,'.0','.') )
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Oh, Jeff, where you lead him?
What he's done to you?
DECLARE @IPAddress VARCHAR(15)
SET @IPAddress = '5.152.61.94'
select @IPAddress,
CONVERT(int,
CONVERT(binary(1), convert(tinyint, PARSENAME(@IPAddress, 4))) +
CONVERT(binary(1), convert(tinyint, PARSENAME(@IPAddress, 3))) +
CONVERT(binary(1), convert(tinyint, PARSENAME(@IPAddress, 2))) +
CONVERT(binary(1), convert(tinyint, PARSENAME(@IPAddress, 1)))
)
Very similar to David's method.
_____________
Code for TallyGenerator -
If, and only if, the OP really wants to do something similar to what I outlined (I am not aware of any other common use for this type of IP manipulation, but I'm sure there are some), he'll want to stick with my method, as that's a fairly standard calculation from an IP address to what is commonly known as an IP "number".
You can see it used in the following places as an example (and you'll see the ranges I spoke about in action):
http://www.hackingspirits.com/cyb_forensic/fsic_articles/loc_place.html
http://www.maxmind.com/app/csv
The fact that parsename works so well for this is simply a lucky byproduct, as I'm pretty sure MS never intended for it to be misused in this way.
-
But, doing it that way, it can't be turned back into an IP address, can it? And, theoretically, couldn't there be dupes that way? After all, the OP did say that turning it back from decimal to IP Address was a requirement...Oh hell... I get it... thanks...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Heh... if that's what he meant by decimal... Me? Guess it's obvious that I don't normally do those kinds of conversions, huh?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Hey guys, thanks for responding.
what about from a number to a real IP?
And what about Mac?
-
Have fun!
DECLARE
@IPAddr
varchar(15)
,@IPNumber bigint
,@a bigint
,@b-2 bigint
,@C bigint
,@D bigint
,@AMulti bigint
,@BMulti bigint
,@CMulti bigint
SET
@IPAddr = '205.152.61.94'
SELECT
@AMulti
= 16777216
,@BMulti = 65536
,@CMulti = 256
-- Convert to IP Number
SELECT
= Cast(Parsename(@IPAddr,4) * @AMulti AS bigint)
,@b-2 = Cast(Parsename(@IPAddr,3) * @BMulti AS bigint)
,@C = Cast(Parsename(@IPAddr,2) * @CMulti AS bigint)
,@D = Cast(Parsename(@IPAddr,1) AS bigint)
SET
@IPNumber = @a + @b-2 + @C + @D
SELECT
@IPNumber
-- Convert back to original IP Address
SELECT
@IPAddr
= Cast((@IPNumber / @AMulti) % 256 AS varchar(15)) + '.' +
Cast((@IPNumber / @BMulti) % 256 AS varchar(15)) + '.' +
Cast((@IPNumber / @CMulti) % 256 AS varchar(15)) + '.' +
Cast(@IPNumber % 256 AS varchar(15))
SELECT
@IPAddr
-
For Mac - see my 1st post.
For reversal conversion - take some programming lessons.
Anybody who can program and has been given with forward transition can build backward one. It's one of mandatory exercise for newbie programmers.
_____________
Code for TallyGenerator -
Hi Sergiy,
I know you mean to help, but your tone is not nice.
Your first post is:
SELECT ID -- integer identity field
FROM dbo.IP_Adresses
WHERE IP_Address = @IP_Address
Honorsly I have no idea about it, where is table IP_Address? what the query can do? is it related to any conversion? I don't see it.
Correct me if I am wrong.
THanks anyway.
-
halifaxdal, in case you missed it, your reversal routine can be found in my code above.
-
Additionally, to tackle the MAC address, I'd probably do something similar to my other code, but with a hex conversion function. Since each of the 6 sets of 2 hex digits are in fixed positions, you can substring them out. After deconverting each set to decimal, I'd take the left most set, multiply by 1, the second leftmost by 256, the third by 65536, fourth by 16777216, fifth by 4294967296, and sixth by 1099511627776. That would allow you to reverse it out in the same manner as I did above, convert the value back to hex, then put it together again. Since bigint can handle all 18 digit integers (as well as most 19 digit ones), it's plenty big enough even for holding MACs.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply