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,O SPlatform, MAC FROM Hosts
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,O
you might, additionally, apply the rtrim() function...
SELECT HostId,NetBiosName,Users,O SPlatform, rtrim(Cast(MAC As NVarchar(12))) FROM Hosts
<no points, please>
SELECT HostId,NetBiosName,Users,O
<no points, please>
you might, additionally, apply the rtrim() function...
SELECT HostId,NetBiosName,Users,O SPlatform, rtrim(Cast(MAC As NVarchar(12))) FROM Hosts
<no points, please>
SELECT HostId,NetBiosName,Users,O
<no points, please>
sorry for the duplicate post...
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.
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.
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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..
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?
can you explain that? not sure what you actually want to achieve with a "cast" on top of that?
ASKER
here is my current query
SELECT CAST(MAC as nvarchar) AS MACADD,HostID, NetbiosName,Users, NetworkAddress,FriendlyNam e, OSPlatform, OSVersion, Domain, OUI,RogueType, LastDetectTime FROM Hosts
SELECT CAST(MAC as nvarchar) AS MACADD,HostID, NetbiosName,Users, NetworkAddress,FriendlyNam
sorry, but that does not explain what you actually need the "cast on top of <that>" ?
SELECT HostId,NetBiosName,Users,O