none
Chinese characters issue with T-SQL.

    Question

  • I am using SQL 2005 and I have table that stores multilingual characters into nvarchar columns. I have a SP that take nvarchar value and return matching result. When I pass Chinese characters to the SP then that SP is retuning no results, however it is working fine for English characters.

    When I print the dynamic SQL inside the SP then it is displaying “?” characters instead of Chinese characters.

    Please help me to resolve this issue. It is really stopping our development and deployments.

    Thanks in advance.

    Thursday, February 11, 2010 4:14 PM

Answers

  • I am sorry, I do not know what is happening in your code.  Of course, the code you use is probably not identical to the test code that I am running.  However, here is what I get after I change the insert to put Unicode data into both PrimaryContent and TranslatedContent.



    INSERT INTO dbo.ABC (PrimaryContent, TranslatedContent) VALUES (N'資料來源:聯博',N'資料來源:聯博')
    INSERT INTO dbo.ABC (PrimaryContent, TranslatedContent) VALUES (N'空炮鸡蛋',N'空炮鸡蛋')

    EXEC
    ABC_SEARCH N'資料來源:聯博' -- Returns 1 row

    EXEC ABC_SEARCH '資料來源:聯博' -- Returns 0 rows

    Have you run my code exactly as I sent it?  If so, I do not know why you are getting different results,  It could be because of some other collation or language difference in the installation or the connect string, I suppose.

    RLF
    Friday, February 12, 2010 9:41 PM

All replies

  • Can you post the SP code? Check that you pass NVARCHAR parameters, it should work everything is passed correctly.
    Plamen Ratchev
    Thursday, February 11, 2010 4:57 PM
  • Hi

    Could you just give this  a try ...some how i feel issue  is w.r.t  OS. ( Not sure.. sorry if i am wrong)...

    try adding language files for all related lanugages in your windows operating system.
    Go to Control Panel-> Regional and Language Option

    Go to Languages tab.

    Go to supplement language option.
    Select both two checkboxes click OK.




    Regards,
    Ramakrishna
    Thursday, February 11, 2010 5:55 PM
  • This works for me as shown below:

    use tempdb
    go
    create table zhongwen(mingzi nvarchar(10))
    go
    insert into zhongwen values (N'有方')
    insert into zhongwen values (N'李杰')
    insert into zhongwen values (N'空炮鸡蛋')
    go
    select * from zhongwen
    
    go
    create procedure zhongwenfind 
     (@mingzi nvarchar(10))
     AS
     SELECT mingzi FROM zhongwen 
     WHERE mingzi  = @mingzi  
    go
    exec zhongwenfind N'李杰'
    go
    drop table zhongwen
    go
    drop procedure zhongwenfind
    go

    However, if you try to pass a Chinese literal as '李杰' instead of N'李杰' it will turn into '??'.

    RLF

    Thursday, February 11, 2010 6:00 PM
  • Thanks for a quick response.

    Here is my table:
    CREATE TABLE [dbo].[ABC](
     [ID] [uniqueidentifier] NOT NULL CONSTRAINT [DF_ABC_ID]  DEFAULT (newid()),
     [PrimaryContent] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
     [TranslatedContent] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL)

    Here is my SP:
    ALTER PROCEDURE [dbo].[ABC_Search]

     @LanguageContent AS NVARCHAR(4000)
    )
    AS 
    BEGIN 
     SET NOCOUNT ON;

     SET @LanguageContent = REPLACE(@LanguageContent,'''','''''')
     SET @LanguageContent = REPLACE(@LanguageContent,'[','[[]')
     SET @LanguageContent = '%' + @LanguageContent + '%';
     SET @DynamicSQL = '
      SELECT 
       ID,  
       PrimaryContent, 
       TranslatedContent
      FROM 
       TranslationDictionary 
      WHERE
       (PrimaryContent LIKE '''+@LanguageContent+'''   
       OR TranslatedContent LIKE '''+@LanguageContent+''')
       AND PrimaryContent != ''''
      ORDER BY
       PrimaryContent ASC;'

     EXEC (@DynamicSQL);
    END;

    Here is value that we are passing from the ASP.NET code 資料來源:聯博

    SP is returning zero rows to application.

    Friday, February 12, 2010 6:46 PM
  • Russell,
    Thanks for your response. 
    It is working fine with or without ‘N’, if I search for the characters that you have inserted.
    However if I use
    資料來源:聯博 then it is returning nothing.

    Friday, February 12, 2010 6:55 PM
  • Yes, your code is losing the UNICODE.   I changed it a little bit to keep the Unicode within the Dynamic-SQL.  You will note that within the dynamic SQL I added the Unicode specifier N before concatenating @LanguageContent into the string.

    CREATE TABLE [dbo].[ABC](
     [ID] [uniqueidentifier] NOT NULL CONSTRAINT [DF_ABC_ID]  DEFAULT (newid()),
     [PrimaryContent] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
     [TranslatedContent] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL)
    
    INSERT INTO dbo.ABC (PrimaryContent, TranslatedContent) VALUES ('資料來源:聯博',N'資料來源:聯博')
    INSERT INTO dbo.ABC (PrimaryContent, TranslatedContent) VALUES ('空炮鸡蛋',N'空炮鸡蛋')
    
    go
    CREATE PROCEDURE [dbo].[ABC_Search]
    (  
     @LanguageContent AS NVARCHAR(4000)
    )
    AS  
    BEGIN  
     SET NOCOUNT ON;
     DECLARE @DynamicSQL NVARCHAR(4000)
    
     SET @LanguageContent = REPLACE(@LanguageContent,'''','''''') 
     SET @LanguageContent = REPLACE(@LanguageContent,'[','[[]')
     SET @LanguageContent = '%' + @LanguageContent + '%'; 
     SET @DynamicSQL = '
      SELECT  
       ID,   
       PrimaryContent,  
       TranslatedContent
      FROM  
       dbo.ABC  
      WHERE
       (PrimaryContent LIKE N'''+@LanguageContent+'''    
       OR TranslatedContent LIKE N'''+@LanguageContent+''')
       AND PrimaryContent != N''''
      ORDER BY
       PrimaryContent ASC;'
     PRINT @DynamicSQL;
     EXEC (@DynamicSQL);
    END;
    GO
    
    EXEC ABC_SEARCH N'來源'
    EXEC ABC_SEARCH '炮鸡'
    
    DROP TABLE DBO.ABC
    DROP PROCEDURE [dbo].[ABC_Search]


    How you pass the parameters is also important.  Here is the code that is actually running in my sample.  You will notice that the first call to ABC_SEARCH uses a Unicode string, while the second does not.    The generated code is:

    -- Unicode parameter
      SELECT  
       ID,   
       PrimaryContent,  
       TranslatedContent
      FROM  
       dbo.ABC  
      WHERE
       (PrimaryContent LIKE N'%來源%'    
       OR TranslatedContent LIKE N'%來源%')
       AND PrimaryContent != N''
      ORDER BY
       PrimaryContent ASC;
    
    -- Non-Unicode parameter
      SELECT  
       ID,   
       PrimaryContent,  
       TranslatedContent
      FROM  
       dbo.ABC  
      WHERE
       (PrimaryContent LIKE N'%??%'    
       OR TranslatedContent LIKE N'%??%')
       AND PrimaryContent != N''
      ORDER BY
       PrimaryContent ASC;
    

    Therefore, with the Unicode parameter it finds the matching string in TranslatedContent.  But with the character parameter it finds the question marks in the PrimaryContent.

    Really, your other REPLACE and concatenate strings should all also be Unicode  (e.g. N'''') rather than character.   Also, concatenating the string passed from a caller leaves you open to SQL Injection, so you should take steps to protect yourself from that.

    Hope that helps,
    RLF

    Friday, February 12, 2010 7:23 PM
  • I am sorry, I do not know what is happening in your code.  Of course, the code you use is probably not identical to the test code that I am running.  However, here is what I get after I change the insert to put Unicode data into both PrimaryContent and TranslatedContent.



    INSERT INTO dbo.ABC (PrimaryContent, TranslatedContent) VALUES (N'資料來源:聯博',N'資料來源:聯博')
    INSERT INTO dbo.ABC (PrimaryContent, TranslatedContent) VALUES (N'空炮鸡蛋',N'空炮鸡蛋')

    EXEC
    ABC_SEARCH N'資料來源:聯博' -- Returns 1 row

    EXEC ABC_SEARCH '資料來源:聯博' -- Returns 0 rows

    Have you run my code exactly as I sent it?  If so, I do not know why you are getting different results,  It could be because of some other collation or language difference in the installation or the connect string, I suppose.

    RLF
    Friday, February 12, 2010 9:41 PM