Any way to avoid prefixing N character for unicode

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 2005 Forums

Other SQL Server Topics (2005)

Any way to avoid prefixing N character for unicode

Author

Topic

ketansoneji
Starting Member

4 Posts

Posted - 2010-10-11 : 05:48:01

I have to support unicode data storage and retrieval in my application

My database tables already have nchar, nvarchar columns.

As per my knowledge, I have to prefix the values for these columns with N character in my insert/update/select statements.

But my existing select, inserts and update statements do not have the N character prefixed to the values.

Is there any way that I can set up some parameter or change configuration at the database server level so that I do not need to change all my SQLs to prefix the unicode data with N character?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-10-11 : 06:30:26

You don't need to use the N prefix, if the column is declared nchar/nvarchar the value will be converted implicitly.

ketansoneji
Starting Member

4 Posts

Posted - 2010-10-11 : 06:55:07

I tried doing it without specifying the N character but it did not work.

For example the following insert statement did not store the value properly:
insert into aaa values ('some value in chinese language')

But when i executed the following query then it stored the data perfectly.
insert into aaa values (N'some value in chinese language')

Again, I had to use N character in my SELECT statement to verify it:
select * from aaa where value = N'some value in chinese language'

I could not post my actual insert statements because the chinese text got converted into ? characters on submitting the post.

I hope I am not missing anything.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-10-11 : 12:43:32

Ahhh, if you are inserting a value that's not supported by the default collation of your database or the column being inserted then yes, you will have to prefix it with N.

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2010-10-12 : 02:54:42

Robvolk....out of curiosity then, is the long-term solution to (avoiding using the 'N') to change the collation of the column/database?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-10-12 : 07:52:23

That might fix it, I haven't worked with different collations enough to be able to confirm it. Sounds like a fun thing to try when I get to the office...

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-10-12 : 09:42:44

Nope, that isn't enough to resolve it:

declare @t table(e nvarchar(100), c nvarchar(100) collate Chinese_PRC_Stroke_90_CI_AI_WS)
insert @t values('abc','??/??')
insert @t values('xyz',N'??/??')

select * from @t

e          c
---------- ----------
abc        ??/??
xyz        ??/??

edit: Huh, that previewed the Chinese characters correctly. Anyway, the results look like this:

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources