Re: Unicode in SQL queries

• Previous message: Raghunandan S via AdminLife: "SQL server returning db error 10025"
• In reply to: Silvio Lopes de Oliveira: "Unicode in SQL queries"
• Next in thread: KA: "Re: Unicode in SQL queries"
• Messages sorted by: [ date ] [ thread ]

Date: Sat, 5 Jun 2004 22:38:08 +0000 (UTC)

[posted and mailed, please reply in news]

Silvio Lopes de Oliveira (silviol@aaesys.com) writes:
> I use Unicode in my program to enter data in Chinese into a nvarchar
> field. When inserting or updating records, my application does not use
> Unicode escape sequences but rather the Unicode characters themselves.
> Thus, my app may have SQL statements like:
>
> UPDATE table SET field1 = '<some string in Chinese>';
>
> The syntax above works both in my app and through SQL Server Enterprise
> Management. However, if I try the SELECT query below, it fails to match
> the record I inserted both in my app and through Enterprise manager:
>
> SELECT * FROM table WHERE field1 = '<some string in Chinese>';

I would expect that you would have to use N'<some string in Chinese>',
or else the string would be converted to fallback characters in your
ANSI character set.

When you look at the data as you updated it, do you see it correctly?
 
> I tried changing the UPDATE statement and the SELECT statement to use
> escape sequences instead of the actual unicode characters, but that made
> things worse. For example, I executed:
>
> UPDATE table SET field1 = '\u4E0A\u6D77';
> SELECT * FROM table WHERE field1 = '\u4E0A\u6D77';

Of course. This is nothing which is supported by SQL Server. The way
to express that string through codes would be:

      select nchar(0x4E0A) + nchar(0x6D77)
 
> Why can I use the Unicode chars explicitly when adding or updating
> records, but cannot use them to match a record with a SELECT statement?
> Is there something I'm missing when using the escape sequence (maybe
> some conversion function) ?

There is no difference to UPDATE and SELECT as far as SQL Server is
concerned. But since you talk about application and Enterprise Manager
there may be things going on at client level.

The best tool to use for simple tests is Query Analyzer, since it
just sends the query to SQL Server and presents the result.

-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

• Previous message: Raghunandan S via AdminLife: "SQL server returning db error 10025"
• In reply to: Silvio Lopes de Oliveira: "Unicode in SQL queries"
• Next in thread: KA: "Re: Unicode in SQL queries"
• Messages sorted by: [ date ] [ thread ]