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.

score 16 · Answer 1 · edited Jun 20 '20 at 09:12

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.

@gdoron - Not really. C# just happens to be the vehical for the SQL statement. — Oded, Dec 22 '12 at 21:10
@Oded : I have no idea how you figured out OP uses SQLServer. +1 for that — a1ex07, Dec 22 '12 at 21:11
One day I used Mysql with C#. And now I use C# with Oracle ... Is it not allowed ? :) Just kidding. — a1ex07, Dec 22 '12 at 21:14

score 0 · Answer 2 · edited May 23 '17 at 11:53

Well putting N prefix front of your Chinese texts is acceptable. You add this prefix to convert it. However you may want to know that there is a downside to it.

Prefix strings with N when they are destined for an nvarchar(...) column or parameter. If they are destined for a varchar(...) column or parameter, then omit it, otherwise you end up with an unnecessary conversion.

Reference: is there a downside to putting N in front of strings in scripts? Is it considered a "best practice"?

score 0 · Answer 3 · answered Oct 20 '20 at 11:47

I run into this issue today with my Java-MySQL application, and I think it has nothing to do with the programming language, so it can work well in the question's situation. Changing the database connection url from

jdbc:mysql://foo:3308/bar

to

jdbc:mysql://foo:3308/bar?characterEncoding=utf8

and everything will work fine.

SELECT .... WHERE something equals with Chinese characters

3 Answers3