Character encoding in SQL Server 2012

Question

I have a database (SQL Server 2012) which has a lot of Unicode data. records inserted from a web application but character are saved in web character encoding 'western' but they are Unicode when this data shown in browsers, browsers automatically change their character encoding to 'Unicode' and show the characters correct but I want to use this data in my application and I want to change them in real Unicode characters

For examples character are saved in tables like this (with 'western' encoding):

Ù¾ÛŒØ´Ø±ÙØª 50 Ø¯Ø±ØµØ¯ÛŒ Ù…Ø®Ø²Ù† Ø°Ø®ÛŒØ±Ù‡ Ø¢Ø¨ Ø¨ÙˆØ´Ù‡Ø±

and I want to convert them into unicode encoding like this :

پیشرفت 50 درصدی مخزن ذخیره آب بوشهر

What datatype are your columns that store this data? In order to store and successfully retrieve Unicode characters, you must use NVARCHAR columns. — marc_s
– marc_s, Commented Dec 25, 2013 at 12:40
If they display on the web pages fine it must be stored correctly. The problem must be how your application handles them. Show us that. nvarchar is stored as UCS-2. This is not configurable. — Martin Smith
– Martin Smith, Commented Dec 25, 2013 at 12:46
If your columns are in fact NVARCHAR , then there is no encoding for those columns - they are Unicode - period. Were these column possibly of type varchar before and then converted to nvarchar after data had already been populated? — marc_s
– marc_s, Commented Dec 25, 2013 at 12:54
thats not application which handle it correctly ,thats the benefit of advanced browsers which detect charcter encooding correctly, so i want to have correct characters stored in my database ,because i want to show them in aother application which is not web based ! — eshirvana
– eshirvana, Commented Dec 25, 2013 at 12:56

M.Ali · Accepted Answer · 2013-12-25 17:05:29Z

1

Like Martin Smith and marc_s has explained you will need to use NVARCHAR datatype if you will be working with unicode character. Or you can use VARCHAR datatype with specific Arabic collation something like this

CREATE TABLE #TestTable
(
Column1 VARCHAR(100) COLLATE Latin1_General_100_CI_AI,  --<-- VARCHAR Column with Default Collation
Column2 VARCHAR(100) COLLATE Arabic_CI_AI_KS_WS,        --<-- VARCHAR Column with Arabic Collation
Column3 NVARCHAR(100),                                  --<-- NVARCHAR Column with Default Collation
Column4 NVARCHAR(100) COLLATE Arabic_CI_AI_KS_WS        --<-- NVARCHAR Column with Arabic Collation
)
INSERT INTO #TestTable 
VALUES(N'پیشرفت 50 درصدی مخزن ذخیره آب بوشهر'
       ,N'پیشرفت 50 درصدی مخزن ذخیره آب بوشهر'
       ,N'پیشرفت 50 درصدی مخزن ذخیره آب بوشهر'
       ,N'پیشرفت 50 درصدی مخزن ذخیره آب بوشهر')

SELECT * FROM #TestTable

Result

Column1                               Column2                               Column3                             Column4
?????? 50 ????? ???? ????? ?? ?????   پيشرفت 50 درصدي مخزن ذخيره آب بوشهر   پیشرفت 50 درصدی مخزن ذخیره آب بوشهر پیشرفت 50 درصدی مخزن ذخیره آب بوشهر

edited Dec 25, 2013 at 17:05

answered Dec 25, 2013 at 13:14

M.Ali

69.9k17 gold badges110 silver badges133 bronze badges

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

3 Comments

Martin Smith Over a year ago

I don't think this is the issue. Notice your example has question marks not random characters. They must be doing something else wrong.

M.Ali Over a year ago

@MartinSmith yes you are right, I think he has got some other Collation other then default or arabic , I reckon doing as I have suggested will fix the issue.

eshirvana Over a year ago

as i explained in comment ,already the datatype is NVARCHAR but characters are stored with western encoding , so i need to convert this character to Unicode!

Collectives™ on Stack Overflow

Character encoding in SQL Server 2012

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related