Unicode in SQL queries

[Silvio Lopes de Oliveira]

Hello,

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 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';

The UPDATE statement actually stored the literal '\u4E0A\u6D77' without
converting it to its Unicode character. The SELECT statement also did not
convert the escape sequence, so it matched the record I inserted but I no
longer have the correct Unicode string stored. I also tried:

UPDATE table SET field1 = N'\u4E0A\u6D77' ;
SELECT * FROM table WHERE field1 = N'\u4E0A\u6D77' ;

with no success either.

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) ?
Thanks,
SL

[Erland Sommarskog]

[posted and mailed, please reply in news]

Silvio Lopes de Oliveira (si*****@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, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[KA]

Prefix the string with N.

UPDATE table SET field1 = '<some string in Chinese>';
should be
UPDATE table SET field1 = N'<??????>';