SELECT .... WHERE something equals with Chinese characters
c#, sql, sql-server, unicode
Solution
This is not a C# issue, but a SQL one.
Make sure that the passed in SQL string is interpreted as a Unicode string in SQL by prepending it with `N` (SQL Server, MySQL):
select * from myTable where category = N'牛奶'
See Constants (Transact-SQL) on MSDN.
Unicode strings
Unicode strings have a format similar to character strings but are preceded by an N identifier (N stands for National Language in the SQL-92 standard). The N prefix must be uppercase. For example, 'Michél' is a character constant while N'Michél' is a Unicode constant. Unicode constants are interpreted as Unicode data, and are not evaluated by using a code page. Unicode constants do have a collation. This collation primarily controls comparisons and case sensitivity. Unicode constants are assigned the default collation of the current database, unless the COLLATE clause is used to specify a collation. Unicode data is stored by using 2 bytes per character instead of 1 byte per character for character data.
Problem
In a C# window application, when I do ``` select * from myTable where category = '牛奶' ``` or ``` select * from myTable where category = 'baby牛奶' ``` The result does not return any rows. But ``` select * from myTable where category = 'baby' ``` This result returns some rows. Could anyone tell me why please? Note: in myTable, some category of column has some values with `牛奶` or `baby牛奶`, and I have no problems displaying Chinese characters on the window application. `牛奶` are Chinese characters.