locked
Can't store chinese in SQL Database RRS feed

  • Question

  • update Food set FoodName =  ' 杏仁' where ID = 100

    in database as ??

    Wednesday, August 23, 2006 8:02 AM

Answers

  •  SuperM wrote:

    update Food set FoodName =  ' 杏仁' where ID = 100

    in database as ??

    I would recommend defining the data column as NVarchar, NChar, or NText, when at all possible when working with non-ascii characters.

    Jimmy

    Friday, August 25, 2006 6:16 PM

All replies

  • Hi SuperM,

    You should use Encoding support from your .NET Application.

    To Encoding:

    Encoding target = Encoding.GetEncoding( "GB18030" );
    Byte[] buffer = target.GetBytes( text );
    return Convert.ToBase64String( buffer );

    From Encoding:

    Encoding target = Encoding.GetEncoding( "GB18030" );
    Byte[] buffer = Convert.FromBase64String( text );
    return target.GetString( buffer );

    You could use GB18030 to simplified or Big5 to traditional.

    Later, value converted you send to store procedure.

    Good Coding!

    Javier Luna
    http://guydotnetxmlwebservices.blogspot.com/

     

    Wednesday, August 23, 2006 8:48 PM
  • Or set the collation settings in SQL Server.  If this is SQL Server 2000, you need to run the rebuildm.exe to change the current collation settings.  But this will affect other databases in your server
    Friday, August 25, 2006 10:30 AM
  • Hi bass,

    "But this will affect other databases in your server".

    This could be very dangerous.

    Good Coding!

    Javier Luna
    http://guydotnetxmlwebservices.blogspot.com/

     

    Friday, August 25, 2006 5:09 PM
  •  SuperM wrote:

    update Food set FoodName =  ' 杏仁' where ID = 100

    in database as ??

    I would recommend defining the data column as NVarchar, NChar, or NText, when at all possible when working with non-ascii characters.

    Jimmy

    Friday, August 25, 2006 6:16 PM
  • I know what you mean but that should have been a part of the planning phase so that it would be considered in the deployment.  Our applications use different collation settings as they are being used in different countries.  So before we even create the databases, the SQL Sever settings have been defined properly.
    Saturday, August 26, 2006 1:35 AM
  • thanks,

    the field needs to store chinese which must define as  NVarchar, NChar, or NText

    The Insert SQL as

    insert into Food (FoodName) values (N'杏仁' )

    The Update SQL  as

    update Food set FoodName =  N' 杏仁' where ID = 100

     

    • Proposed as answer by DaaDa Ng Friday, January 7, 2011 7:45 AM
    Monday, August 28, 2006 3:23 AM
  • Hi,

    I'm using SQL Server 2005. I've added one column having datatype NVarchar(200) to store japanese character with default collation SQL_Latin1_General_CP1_CI_AS

    When I'm updating table column value using below sql statement

    update tblClient set NameInJapanese = N'準気配', it's working fine.

    However, I've to pass this value using Stored procedure.

    In SP i've written statement as below

     

    update tblClient set NameInJapanese = N''+ @NameInJapanese + '' where clientid = 2

    Still it's showing me value as '???'. I've changed column collation to 

    Japanese_CI_AS , eventhough, it's showing me '???' values.

    Can someone please reply on this?

    Wednesday, December 8, 2010 6:42 AM
  • I also had same issue as Kaushal is facing. Please check that column type in which u r inserting japnese text is NVARCHAR and parameter type for @NameInJapnese should also be NVarChar. Change the types to NVARCHAR and if ur using service, restart the service.

    It helped me in solving the problem.

    • Proposed as answer by AanchalT Tuesday, March 22, 2011 5:30 AM
    Monday, March 21, 2011 8:58 AM
  • I too have had the same problem. I implemented SuperM's solution

    insert into Food (FoodName) values (N'杏仁' )

    and this works fine.

    I have setup the fields that store Chinese characters to be NVARCHAR and NTEXT and my Collation is set to Latin1_General_CI_AS.

    My concern is that I have inserted Chinese characters into SQL before and (as far as I can remember) not had to put N prior to the Chinese that I want to insert.

    Can anyone explain if there is an alternative method to the one I am using?

    Tuesday, October 25, 2011 10:39 PM