7

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.

Boris
  • 9,986
  • 34
  • 110
  • 147

3 Answers3

19

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.

Damien_The_Unbeliever
  • 234,701
  • 27
  • 340
  • 448
  • Thank you for your answer. The field Name in my Sql table is of type nvarchar(50), so the problem remains the same. As for the collate, I reckon that should resolve the second question, however, until I resolve the `ć` and other missing characters, I am stuck. I have checked my Sql command text in the code and it appears normal (meaning it contains those pesky characters). Could there be anything else that is preventing the saving of those characters? How come that the SELECT statement also converts `ć` to `c` (I am not using COLLATE)? – Boris Nov 10 '10 at 15:28
  • 1
    @Boris - like I say (point 2), if you've got string literals (as in the examples you've posted), you need to put a "N" before the opening quote mark, otherwise the string will be converted into another form before it gets to the table. – Damien_The_Unbeliever Nov 10 '10 at 15:46
  • OK, I got it! Should I place that `N` in front of each field value? – Boris Nov 10 '10 at 15:58
1

To allow SQL Server to store special characters, use nvarchar instead of varchar for the column type.

When retrieving, you can force a accent-insensitve collation so that it ignores the different C's:

WHERE Name = 'Boris Borenović' COLLATE Cyrillic_General_CI_AI

Here, CI stands for Case Insensitive, and AS for Accent Insensitive.

Andomar
  • 232,371
  • 49
  • 380
  • 404
0

I've faced with the same problem and after some researching:

https://dba.stackexchange.com/questions/139551/how-do-i-set-a-sql-server-unicode-nvarchar-string-to-an-emoji-or-supplementary

What is the difference between varchar and nvarchar?

I altered type of needed fields:

ALTER TABLE [table_name] ALTER COLUMN column_name [nvarchar] 
GO

And it works!

Taras Melnyk
  • 3,057
  • 3
  • 38
  • 34