Using Cast to convert nchar - > nvarchar

Hi guys!

im a real dummy when it comes to databases and need some help with a simple query!

im trying to query a DB which has a list of "rogue" computers in the company (kinda nac solution) - but whenever i query the MS SQL Db - i get one row which is empty (THE Mac address row)
anyhows , from here to there i talked to a few guys who said there is a problem with the agent driver im using and the simplest way to ovveride this is to use the CAST command.

here is the query , how do i use cast in order to convert the ROW called MAC from nchar12 to nvarchar?

SELECT HostId,NetBiosName,Users,OSPlatform,MAC FROM Hosts


m0tekAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
what about this:
select substring(mac, 1,2) + ':' + substring(mac, 3,2) + ':' + substring(mac, 5,2) + ':' + substring(mac, 7,2) + ':'  + substring(mac, 11,2) + ':' + substring(mac, 13,2)  as mac_formatted
from yourtable
0
 
Jim P.Commented:

SELECT HostId,NetBiosName,Users,OSPlatform,Cast(MAC As NVarchar(12)) FROM Hosts

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you might, additionally, apply the rtrim() function...

SELECT HostId,NetBiosName,Users,OSPlatform, rtrim(Cast(MAC As NVarchar(12))) FROM Hosts

<no points, please>
0
Network Scalability - Handle Complex Environments

Monitor your entire network from a single platform. Free 30 Day Trial Now!

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you might, additionally, apply the rtrim() function...

SELECT HostId,NetBiosName,Users,OSPlatform, rtrim(Cast(MAC As NVarchar(12))) FROM Hosts

<no points, please>
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
sorry for the duplicate post...
0
 
m0tekAuthor Commented:
what does RTRIM do?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
would trim away trailing spaces...
0
 
Jim P.Commented:
The char data type is a fixed width fields. Any data stored in them is automatically padded to the length of the field. So a say you have a char(12) field and the word green is in it, it will actually be stored as "green       " where a varchar(12) would store it as "green".

The nchar and nvarchar have similar properties but are the unicode version if you are using a foreign language. They actually double the data space in use. If you could see the actual data an nchar(12) would look like "g r e e n               " and an nvarchar would be "g r e e n  ". The foreign language version would be stored in the empty spaces.
0
 
m0tekAuthor Commented:
Ok ,
with nchar i get the mac as an empty field
with nvarchar i get the mac as aletters field (which is good) but no ":" , is there a way to create a ":" between each 2 charecters?

for now im getting
00bb00ccdd00ff
and id' like to see
00:bb:00:cc:dd....etc

0
 
m0tekAuthor Commented:
i need to use cast

select substring(mac, 1,2) + ':' + substring(mac, 3,2) + ':' + substring(mac, 5,2) + ':' + substring(mac, 7,2) + ':'  + substring(mac, 11,2) + ':' + substring(mac, 13,2)  as mac_formatted
from yourtable

on top of it..
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>I need to use cast
can you explain that? not sure what you actually want to achieve with a "cast" on top of that?
0
 
m0tekAuthor Commented:
here is my current query

SELECT CAST(MAC as nvarchar) AS MACADD,HostID, NetbiosName,Users, NetworkAddress,FriendlyName, OSPlatform, OSVersion, Domain, OUI,RogueType, LastDetectTime FROM Hosts
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
sorry, but that does not explain what you actually need the "cast on top of <that>" ?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.