SELECT .... WHERE something equals with Chinese characters

Question

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.

Answer 1

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.