Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Using Cast to convert nchar - > nvarchar

Posted on 2007-10-18
13
Medium Priority
?
1,189 Views
Last Modified: 2012-08-13
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


0
Comment
Question by:m0tek
  • 7
  • 4
  • 2
13 Comments
 
LVL 38

Expert Comment

by:Jim P.
ID: 20100470

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

0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20100650
you might, additionally, apply the rtrim() function...

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

<no points, please>
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20100706
you might, additionally, apply the rtrim() function...

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

<no points, please>
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20100844
sorry for the duplicate post...
0
 

Author Comment

by:m0tek
ID: 20101166
what does RTRIM do?
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20101296
would trim away trailing spaces...
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 20101381
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
 

Author Comment

by:m0tek
ID: 20102225
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
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 20102391
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
 

Author Comment

by:m0tek
ID: 20117464
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20117654
>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
 

Author Comment

by:m0tek
ID: 20117762
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20119105
sorry, but that does not explain what you actually need the "cast on top of <that>" ?
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In today's business world, data is more important than ever for informing marketing campaigns. Accessing and using data, however, may not come naturally to some creative marketing professionals. Here are four tips for adapting to wield data for insi…
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question