Link to home
Start Free TrialLog in
Avatar of m0tek
m0tek

asked on

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


Avatar of Jim P.
Jim P.
Flag of United States of America image


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

Avatar of Guy Hengel [angelIII / a3]
you might, additionally, apply the rtrim() function...

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

<no points, please>
you might, additionally, apply the rtrim() function...

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

<no points, please>
sorry for the duplicate post...
Avatar of m0tek
m0tek

ASKER

what does RTRIM do?
would trim away trailing spaces...
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.
Avatar of m0tek

ASKER

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

ASKER CERTIFIED SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of m0tek

ASKER

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..
>I need to use cast
can you explain that? not sure what you actually want to achieve with a "cast" on top of that?
Avatar of m0tek

ASKER

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
sorry, but that does not explain what you actually need the "cast on top of <that>" ?