My database contain both chinese and english datas. When working with select command i am able to retrieve only english datas eventhough i have made all the datatypes nvarchar.
I am using a stored procedure to retrieve the data
declare @chinese nvarchar
select field_name from Table_name
where name like @chinese+'%'
The qurey is working if we give
select * from table name where name like N'政府机关'
If you think about the way that the percent sign (%) matches, you have two very different queries. The second query isn't using any wildcard at all, it could use = as well as it uses LIKE. The first query is using a wildcard after the constant, so it will find any string that STARTS with the characters you provide.
Interesting! If you check the BOL comments about LIKE matching, there are some differences between the way that LIKE is processed for ASCII data and Unicode data, especially regarding trailing blanks. Could this be your problem?
What tool (Query Analyzer, OSQL, Visual Studio, etc) are you using to submit these queries? It looks to me like it is not passing Unicode either to, from, or both ways to the server. I suspect that there is one piece somewhere in the data transfer that is using UTF-8 encoding or something like it, that breaks the Unicode datapath.
we r using c# .net as front end .
The chinese datas that are present in the database in not entered through the key board. Just cut and paste. So it wont be considered as characters. It will be considered as ascii code. Then i think it will come like this ????. OH no idea..
Thank u very much
Try to put the query of interest into either Query Analyzer, or if you don't have Query Analyzer then use OSQL.EXE from a command prompt. See if the query works correctly in that environment (I strongly suspect that it will).
If the query works Ok there, then the problem is almost certainly that something in the C# code is treating the data as 8 bit characters instead of Unicode. It only takes ONE place that treats the string as 8 bit anywhere in the datapath to reduce the entire datapath to handling only 8 bit data!