CODEWITHSUNDEEP
sql-serverlocalizationdiacritics
Leah
Leah

Reputation: 2607

Multi-language support

We have developed a site that needs to display text in English, Polish, Slovak and Czech. However, when the text is entered into the database, any accented letters are changed to english letters.

After searching around on forums, I have found that it is possible to put an 'N' in front of a string which contains accented characters. For example:

INSERT INTO Table_Name (Col1, Col2) VALUES (N'Value1', N'Value2')

However, the site has already been fully developed so at this stage, going through all of the INSERT and UPDATE queries in the site would be a very long and tedious process.

I was wondering if there is any other, much quicker, way of doing what I am trying to do?

The database is MSSQL and the columns being inserted into are already nvarchar(n).

Upvotes: 2

Views: 3695

Answers (2)

Gennady Vanin Геннадий Ванин
Gennady Vanin Геннадий Ванин

Reputation: 10384

Topic-starter wrote:

"text in English, Polish, Slovak and Czech. However, when the text is entered into the database, any accented letters are changed to english letters" After searching around on forums, I have found that it is possible to put an 'N' in front of a string which contains accented characters. For example:

INSERT INTO Table_Name (Col1, Col2) VALUES (N'Value1', N'Value2')

"The collation for the database as a whole is Latin1_General_CI_AS"

I do not see how it could happen due to SQL Server since Latin1_General_CI_AS treats european "non-English" letters: 

--on database with collation Latin1_General_CI_AS
declare @test_multilanguage_eu table
(
     c1 char(12),
     c2 nchar(12)
)

INSERT INTO @test_multilanguage_eu  VALUES ('éÉâÂàÀëËçæà', 'éÉâÂàÀëËçæà')

SELECT c1, cast(c1 as binary(4)) as c1bin, c2, cast(c2 as binary(4)) as c2bin 
FROM @test_multilanguage_eu

outputs: 

c1           c1bin      c2           c2bin
------------ ---------- ------------ ----------
éÉâÂàÀëËçæà  0xE9C9E2C2 éÉâÂàÀëËçæà  0xE900C900

(1 row(s) affected)

I believe you simply have to check checkboxes them Control Panel --> Regional and Language Options --> tab Advanced --> Code page conversion tables and check that you render in the same codepage as you store it.
Converting to unicode from encodings used by clients would lead to problems to render back to webclients, it seems to me.

I believe that most European collation designators use codepage 1252 [1], [2].

Update:
SELECT COLLATIONPROPERTY('Latin1_General_CI_AS' , 'CodePage') outputs 1252

[1]
http://msdn.microsoft.com/en-us/library/ms174596.aspx
[2]
Windows 1252
http://msdn.microsoft.com/en-us/goglobal/cc305145.aspx

Upvotes: 0

Oded
Oded

Reputation: 499002

There isn't any quick solution.

The updates and inserts are wrong and need to be fixed.

If they were parameterized queries, you could have simply made sure they were using the NVarChar database type and you would not have a problem.

Since they are dynamic strings, you will need to ensure that you add the unicode specifier (N) in front of each text field you are inserting/updating.

Upvotes: 1

Related Questions