Any side effects of CAST Nvarchar to VARCHAR? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Any side effects of CAST Nvarchar to VARCHAR?

Author

Topic

Kristen
Test

22859 Posts

Posted - 2013-12-19 : 12:20:56

Some code I am reviewing is pulling data from a 3rd party database and doing:


INSERT INTO MyTable
SELECT CONVERT(varchar(100), TheirNVarcharColumn) AS MyVarcharColumn
FROM OTHER_DATABASE.dbo.SomeTable

Collation on the OTHER_DATABASE is Latin1_General_CI_AS, and on the receiving table is SQL_Latin1_General_CP1_CI_AS

Are there any side effects of doing this?

Risks to wide characters in the Nvarchar that get mucked up? (I guess I can answer my own question on that one - presumably they will come through as weird question-mark characters?)

Any reason not to just use


...
SELECT LEFT(TheirNVarcharColumn, 100)
...

perhaps some differences in behaviour which are potentially significant?

Thanks

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-12-19 : 12:45:12

If there are actual double-byte characters in the source, then you can loose some information.

I can only guess why they decided to do a conversion, but I'd assume they wanted the data as a VARCHAR and not an NVARCHAR. Applying the LEFT function will give the results in NVARCHAR, in this case.

Kristen
Test

22859 Posts

Posted - 2013-12-19 : 12:52:04

Thanks. The destination table's column is VARCHAR, so there has to be a conversion (from Nvarchar). Just wondering if there is a good / bad way to do the conversion?

I would have just used LEFT and left it to SQL to apply a suitable conversion process - but maybe an explicit CAST is better?

Kristen
Test

22859 Posts

Posted - 2013-12-19 : 12:53:30

P.S. I doubt that there are double-byte characters in the data, in practice, but their might be now/in-future.

I'd better set up a deliberate test so that we see how goofy it looks. If someone puts a Chinese name in, and it comes out as "??????" the other end then that won't be any use whatsoever to the users of that part of the system!!

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-12-19 : 13:03:44

Here is a quick demo:

create table #TEMPTABLE (COl1 nvarchar(100), COL2 varchar(100), COL3 varbinary(200), COL4 varbinary(200));
DECLARE @var1 nvarchar(40);
DECLARE @var2 varchar(40);
set @var1 = NCHAR(12481) + NCHAR(12515) + NCHAR(12540) + NCHAR(12523) + NCHAR(12474) + N' Charles';
set @var2 = NCHAR(12481) + NCHAR(12515) + NCHAR(12540) + NCHAR(12523) + NCHAR(12474) + N' Charles';

insert into #TEMPTABLE values (@var1, @var2, convert(varbinary,@var1), convert(varbinary,@var2));

select *
from #TEMPTABLE

drop table #TEMPTABLE

EDIT: Updating Unicode characters.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-12-19 : 13:04:37

Doh.. apparently you can't cut-n-past Japanese characters.

Kristen
Test

22859 Posts

Posted - 2013-12-19 : 13:06:50

Thanks. Any suggestions for the best way to generate some real wide-character values for the "????? Charles"?

Perhaps I can just Cut & Paste from a Chinese web page?

Kristen
Test

22859 Posts

Posted - 2013-12-19 : 13:07:48

P.S. What were you intending was different between @var1 and @var2 contents? Looks like that is lost in translation posting it here

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-12-19 : 13:11:28

I updated my sample data.

You will see that the binary is different and the Japanese (in this case) gets lost in the conversion to VARCHAR.

Kristen
Test

22859 Posts

Posted - 2013-12-19 : 13:11:44

OK, I'm a twit. Figured out that @var1 & 2 are different datatypes. Sorry about that.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-12-19 : 13:14:24

Hehe, all good. I didn't really explain what my sample was showing. :)

Kristen
Test

22859 Posts

Posted - 2013-12-19 : 13:16:13

I added:


UPDATE	#TEMPTABLE
SET	COl2 = CONVERT(varchar(100), COl1)

select *
from #TEMPTABLE

UPDATE	#TEMPTABLE
SET	COl2 = LEFT(COl1, 100)

select *
from #TEMPTABLE

I can't see any difference in the value in COL2

however, I'd still be interested to hear if anyone knows of any side effects or pros/cons, in particular if there is anything goofy that might happen to normal 8-bit characters, or if the difference in Collation is going to muck anything up.

Kristen
Test

22859 Posts

Posted - 2013-12-19 : 13:23:19

quote:
Originally posted by Lamprey

I didn't really explain what my sample was showing

Now you've NCHAR'd the sample data (good idea

) I can now see what your example was Chaaruzu !!

Arigatou!

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-12-19 : 14:20:31

quote:
Originally posted by Kristen

I can't see any difference in the value in COL2

That is what I would expect. In one case you Explicitly casting before updating. In the other LEFT will return an NVARCHAR and then it is getting Implicitly converted to VARCHAR before updating.

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2013-12-19 : 18:31:15

quote:
Originally posted by Kristen
Any reason not to just use

...
SELECT LEFT(TheirNVarcharColumn, 100)
...

No, that would work just fine. SQL will implicitly (automatically) convert it to varchar, since that is the data type of the receiving column.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources