SQL Server & ASP .NET encoding issue

Question

my page has utf-8 meta element added + sql server encoding is also utf. However when I create record and try to issue SELECT statement with condition that contains POLISH characters like 'ń' , I see no results. Any ideas what am I missing?

ALSO Sql management studio shows result with POLISH characters , but I don't trust it.... I guess something is wrong with putting record into database...

Or how can I troubleshoot it?

Thanks,Paweł

Answer 1

I had the same issue, and I solved it by prefixing the text in the WHERE clause with "N".

For example, I have a table 'Person' containing a bit over 21,000 names of people. A person with the last name "Krzemiński" was recently added to the database, and the name appears normal when the row is displayed (i.e., the "ń" character is displayed correctly). However, neither of the following statements returned any records:

SELECT * FROM Person WHERE FamilyName='Krzemiński
SELECT * FROM Person WHERE FamilyName LIKE 'Krzemiń%'

...but these statements both returned the correct record:

SELECT * FROM Person WHERE FamilyName LIKE 'Krzemi%'<br>
SELECT * FROM Person WHERE FamilyName LIKE 'Krzemi%ski'

When I executed the following statement:

SELECT * FROM Person WHERE FamilyName LIKE '%ń%'

I get all 8900 records that contain the letter "n" (no diacritic), but I do not get the record that contains the "ń" character. I tried this last query with all of the Polish characters (ąćęłńóśźż), and all of them except "ó" exhibit the same behavior (i.e., return all records with the lower-ASCII equivalent character). Weirdly, "ó" works as it should, returning only those records with an "ó" in the FamilyName field.

In any case, the solution was to prefix the search criterion with "N", to explicitly declare it as Unicode.

Thus, the following statements:

SELECT * FROM Person WHERE FamilyName LIKE N'%ń%'
SELECT * FROM Person WHERE FamilyName=N'Krzemiński'

...both return the correct set of records.

The reason I was confused is that I have MANY records with weird diacritics, and they all return the correct records even without the "N" prefix. So far, the only characters I've found that require the explicit "N" prefix are the Polish characters.

Answer 2

According to this (Archived) Microsoft Support Issue:

You must precede all Unicode strings with a prefix N when you deal with Unicode string constants in SQL Server

Answer 3

simply use nvarchar instead of varchar as the datatype of the column saving the record.