MsSQL not work with national letter in WebApp

[Ian_Kennedy]

@Tim_Hare

That tells the database, “I promise to store data that is UTF8”. The database will take you at your word. It will not convert any data to UTF8 automatically. You can store any encoding in the column and the database doesn’t care. When you pull data from the table, it’s up to you to know the original encoding so you can handle it correctly.

I’m sorry, but it doesn’t do that at all. It tells the database that the SQL command is using UTF8 character set. Not that the data in the columns are stored in UTF8.

To use UTF8 correctly in a database you need to set the “charset” and “collation” to UTF8. Otherwise your data isn’t expected to be in UTF8 and will not be sorted correctly by an index or order by statement.

The best way of dealing with this is to set the database default to UTF8 before adding any data, and obviously ensure that any data stored in the database is actually using UTF8 encoding when it is stored. Otherwise you end up having to specify every char/varchar/text/longtext etc field is using the correct encoding and collation settings.

Attempting to change such things on an existing database is a complicated task that needs thought and planning. Typically reading the existing data, converting the encoding and storing it back again. It is also vital to not have two, or more, applications that are using different encodings and storing data into the same database tables. Unless, obviously, you enjoy looking at every row / column in a database by hand and ensuring that the data is correctly encoded after the fact. (I’ll leave it to you to decide who had to do this. Thankfully it was a relatively small database) .