text encodings in .net, sql server processing

Question

I have an application that gets terms from a DB to run as a list of string terms. The DB table was set up with nvarchar for that column to include all foreign characters. Now in some cases where characters like ä will come through clearly when getting the terms from the DB and even show that way in the table. When importing japanese or arabic characters, all I see are ????????. Now I have tried converting it using different methods, first converting it into utf8 encoding and then back and also secondly using the httputility.htmlencode which works perfectly when it is these characters but then converts quotes and other stuff which I dont need it to do. Now I accused the db designer that he needs to do something on his part but am I wrong in that the DB should display all these characters and make it easy to just query it and add to my ssearch list. If not is there a consistent way of getting all international characters to display correctly in SQL and VB.net

I know when I have read from text files I just used the Microsoft.visualbasic.textfieldparser reader tool with encoding set to utf8 and this would not be an issue.

gbn · Accepted Answer · 2011-01-07 17:31:50Z

1

If the database field is nvarchar, then it will store data correctly. As you have seen.

Somewhere before it gets to the database, the data is being lost or changed to varchar: stored procedure, parameters, file encoding, ODBC translation etc.

DECLARE @foo nvarchar(100), @foo2 varchar(100)

--with arabic and japanese and proper N literal
SELECT @foo = N'العربي   日本語', @foo2 = N'العربي   日本語'
SELECT @foo, @foo2 -- gives العربي   日本語

--now a varchar literal
SELECT @foo = 'العربي   日本語', @foo2 = 'العربي   日本語'
SELECT @foo, @foo2 --gives ??????   ???

--from my Swiss German keyboard. These are part of my code page.
SELECT @foo = 'öéäàüè', @foo2 = 'öéäàüè'
SELECT @foo, @foo2 --gives ??????   ???

So, apologise to the nice DB monkey... :-)

edited Jan 7, 2011 at 17:31

answered Jan 7, 2011 at 17:10

gbn

434k84 gold badges602 silver badges690 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

vbNewbie Over a year ago

That's what I thought but he keeps saying its fine. Characters like ü,ö are displayed correctly though and when I tried a japanese term which I manually imported into the table it displayed as ????? but when I queried it through vb.net, I saw the japanese characters correctly so I agree it cannot be me. Is there any generic tool to avoid this

vbNewbie Over a year ago

Thanks for your response, very helpful. Do you think there is a way to query it and putting the 'N', in front? Like select Nfoo from table...

gbn Over a year ago

This is not needed: foo is already nvarchar. Somewhere this is being lost after the SQL call

Jitendra · Accepted Answer · 2013-09-19 14:23:10Z

0

Always try to use NVARCHAR or NTEXT to store foreign charactesr. you cannot store UNICODE in varchar ot text datatype. Also put a N before string value

like

UPDATE [USER]
SET Name = N'日本語'
WHERE ID = XXXX;

answered Sep 19, 2013 at 14:23

Jitendra

263 bronze badges

Collectives™ on Stack Overflow

text encodings in .net, sql server processing

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related