converting macaddr to bigint and inserting it to table

Question

converting macaddr to bigint and inserting it to table

feeblemind_99

Old Hand

Points: 319
More actions
April 2, 2008 at 9:16 pm

#188116

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:E7:DE: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 4 posts - 1 through 3 (of 3 total)

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

GSquared SSC Guru Points: 260824 More actions · Answer 1

If you first convert it to a valid hex number (remove the colons), you should then be able to convert it to bigint using any standard hex to dec conversion.

Here's a sample hex to dec converter:

create function Hex_Dec

(@Hex_in varchar(10))

returns bigint

as

begin

declare @Dec bigint, @HexPos char(16)

select @hexpos = '0123456789abcdef'

select @hex_in = reverse(@hex_in)

;with CTE1 (Pos, Sub) as

(select number, substring(@hex_in, number, 1)

from common.dbo.numbers

where number 0),

CTE2 (HexSub) as

(select power(16, pos-1) * (charindex(sub, @hexpos, 0)-1)

from cte1)

select @dec = sum(hexsub)

from cte2

return @dec

end;

It's easy enough to make this one into a set-based solution.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

feeblemind_99 Old Hand Points: 319 More actions · Answer 2

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 3

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

Response over here: http://www.sqlservercentral.com/Forums/Topic479104-149-1.aspx#bm479146