CODEWITHSUNDEEP
c#sqlsql-serverunicode
Kam2012
Kam2012

Reputation: 287

SELECT .... WHERE something equals with Chinese characters

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.

Upvotes: 8

Views: 8445

Answers (3)

Lebecca
Lebecca

Reputation: 2878

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.

Upvotes: 0

Oded
Oded

Reputation: 499002

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.

Upvotes: 19

bonCodigo
bonCodigo

Reputation: 14361

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"?

Upvotes: 0

Related Questions