Unicode characters in Sql table

Question

I am using Sql Server 2008 R2 Enterprise. I am coding an application capable of inserting, updating, deleting and selecting records from a Sql tables. The application is making errors when it comes to the records that contain special characters such as ć, č š, đ and ž.

Here's what happens:

The command:

INSERT INTO Account (Name, Person) 
VALUES ('Boris Borenović', 'True') 
WHERE Id = '1'

inserts a new record but the Name field is Boris Borenovic, so character ć is changed to c.

The command:

SELECT * FROM Account 
WHERE Name = 'Boris Borenović'

returns the correct record, so again the character ć is replaced by c and the record is returned.

Questions:

1. Is it possible to make Sql Server save the ć and other special characters mentioned earlier?
2. Is it still possible, if the previous question is resolved, to make Sql be able to return the Boris Borenović record even if the query asks for Boris Borenovic?

So, when saving records I want Sql to save exactly what is given, but when retrieving the records, I want it to be able to ingnore the special characters. Thanks for all the help.

Answer 1

1) Make sure the column is of type nvarchar rather than varchar (or nchar for char)

2) Use N' at the start of string literals containing such strings, e.g. N'Boris Borenović'

3) If you're using a client library (e.g. ADO.Net), it should handle Unicode text, so long as, again, the parameters are marked as being nvarchar/nchar instead of varchar/char

4) If you want to query and ignore accents, then you can add a COLLATE clause to your select. E.g.:

SELECT * FROM Account 
WHERE Name = 'Boris Borenovic' COLLATE Latin1_General_CI_AI

Where _CI_AI means Case Insensitive, Accent Insensitive, should return all rows with all variants of the "c" at the end.

5) If the column in the table is part of a UNIQUE/PK constraint, and you need it to contain both "Boris Borenović" and "Boris Borenovic", then add a COLLATE clause to the column definition, but this time use a collation with "_AS" at the end, which says that it's accent sensitive.