macaddr to bigint

Question

macaddr to bigint

feeblemind_99

Old Hand

Points: 319
More actions
April 3, 2008 at 5:56 am

#184793

Hi everyone!
Could you help me regarding this one.. I have a table where i sourced my macaddress in the following format (00:02:A5:E7E:40). do you have any means of converting this into integer? pls help.
another thing, in case this macaddr would be successfully converted into integer, how would i insert it to a new table. how would the sql statement go wherein, if the macaddr have contiguous integer values (meaning, incremental of 1), they would be treated as one entry.
Example:
Base Table
macaddrint ResourceId
101 1234
101 1234
102 1234
102 1234
103 1234
104 1234
106 1234
108 1234
Derived tables:
Port Table
id nicid macaddrint
1 1 101
2 1 102
3 1 103
4 1 104
5 2 106
6 3 107
NIC table
nicid
1
2
3
Kindly help me.. Thanks!!

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Todd Engen SSCrazy Eights Points: 9869 More actions · Answer 1

Could you help me regarding this one.. I have a table where i sourced my macaddress in the following format (00:02:A5:E7E:40). do you have any means of converting this into integer? pls help.

I have a suggestion for the first part of your question.

Why not store the MAC as the BINARY(6) value it is? But if you want a decimal value just add an extra step with the CONVERT function after turning it into binary.

You'd need a function like this: usp_hexstrtovarbin

Example: Converting MAC Address to binary.

CREATE TABLE Table1(col1 BINARY(6))

DECLARE @MAC CHAR(20)

SET @MAC = '00:02:A5:E7:40'

SELECT @MAC = '0x'+REPLACE(@MAC,':','')

DECLARE @bin varbinary (4000)

EXEC usp_hexstrtovarbin @MAC, @bin OUTPUT

-- For integer value.

-- SELECT CONVERT(BIGINT,@bin)

INSERT INTO Table1(col1) values(@bin)

Edit: changed binary(8) to binary(6)

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 2

I agree with Todd - it would be a lot easier to identify things if the MAC address looked like what is getting broadcast on the network, wouldn't 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?

Adam Haines SSC-Insane Points: 23217 More actions · Answer 3

This was also posted in the TSQL forums. It is always benifical to place your post in one spot. This way you have all your answers/responses in one spot. GSquared gave an answer in the other forum.

http://www.sqlservercentral.com/Forums/Topic478943-338-1.aspx

edit: forgot the link 🙂

My blog: http://jahaines.blogspot.com

feeblemind_99 Old Hand Points: 319 More actions · Answer 4

Posting same reply as in other same post..

Hi, Thanks for the replies.. yep, I've already converted MACAddr yesterday using almost the same statements that you provided. However, for the second part of my question, do you have any idea on how to do it?

Thanks again!

Todd Engen SSCrazy Eights Points: 9869 More actions · Answer 5

Cross post to: http://www.sqlservercentral.com/Forums/Topic478943-338-1.aspx

Response over here: http://www.sqlservercentral.com/Forums/Topic479104-149-1.asp

For the second part of your question it sounds like you need a single key value to group them by. Since you're looking for consecutive values here's one way to get a "Grouping ID".

WITH cteTable(col1,rownumber) AS

(

SELECT col1, ROW_NUMBER() OVER(ORDER BY col1 ASC) AS rownumber FROM Table1

)

SELECT col1, (col1 - rownumber) AS groupid FROM cteTable

It seems like you have no guarantee that consecutive mac addresses are in a single server. Or that one server hasn't had an additional NIC added. Asking the server what it has would seem to be a more accurate way to match the MAC to it's owner and that could be done with a WMI query.

For example

"SELECT * FROM Win32_NetworkAdapterConfiguration WHERE IPEnabled = True"