March 28, 2011 at 3:31 pm
Hi all,
I have a project I'm working on and need a little help.
I have two tables one is called Audits and the other is Device. the audits tables contains information required for an audit and device contains the information about the computers I use name, ip, Mac address etc. I need to join the two tables on Mac Addresses the problem is on table the mac's are set up with ':' ie 00:03:8H:I2:23 and the other has no ':' ie 0023439D98D.
I was think the replace funciton could work. any suggestion?
March 28, 2011 at 3:55 pm
Yes, work out with REPLACE.. Like : REPLACE(column, ':','')
March 28, 2011 at 4:12 pm
A mac address is a pretty standard thing.
Don't know much about them technically, however looking at this document
http://standards.ieee.org/develop/regauth/tut/macgrp.pdf
I'd be inclined to format toward the standard, using the '-' character instead of ':' however that's up to you.
First check that every mac in Device actually meets the expected format of a string of 12 hex-characters
select *
from Devices
where mac not like '[0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F]'
If that returns nothing then you're good to go
Then:
Splitting with substrings would work.
Ensuring the Devices table has sufficient column width char(17) to insert the ':' then
Try a select to see if it gives you what you're looking for
SELECT mac = SUBSTRING(@mac, 1, 2) + ':' + SUBSTRING(@mac, 3, 2) + ':' + SUBSTRING(@mac, 5, 2) + ':' + SUBSTRING(@mac, 7, 2) + ':' + SUBSTRING(@mac, 9, 2) + ':' + SUBSTRING(@mac, 11, 2)
FROM Devices
If that works, then
UPDATE Devices
SET mac = SUBSTRING(@mac, 1, 2) + ':' + SUBSTRING(@mac, 3, 2) + ':' + SUBSTRING(@mac, 5, 2) + ':' + SUBSTRING(@mac, 7, 2) + ':' + SUBSTRING(@mac, 9, 2) + ':' + SUBSTRING(@mac, 11, 2)
As an aside, whether these tables are temporary (for a single audit) or more permanent, get yourself some constraints on them.
If Devices is a main table, possible a PRIMARY KEY or UNIQUE constraint on mac wouldn't go amiss.
Audit (if using the same format as Devices - which is good practice) can then reference Devices as a FOREIGN KEY.
This would ensure that an Audit record can never be entered against a Device that doesn't exist.
---------------------------------------------
If data can screw you, it will; never assume the data are correct.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply