Results 1 to 9 of 9
  1. #1
    Join Date
    Jun 2002
    Location
    Singapore
    Posts
    71

    Unanswered: varchar to nvarchar

    Hello All.

    I would like to seek your advise on changing the data type from varchar to nvarchar in my sql database table. Will there be any impart on my data? I am changing it because I need to store Chinese, Thai and Japanese characters in one of the customer name field.

    All the above foreign language characters were stored as varchar from day 1 theree years ago and I have decided to change the data type so that foreign language characters could be stored properly from next year.

    I have done a test on a temp table with my existing data (foreign language characters) but they remain the same as before after the data type change. I still don't see them in their own language. But if I load new data from text file, they appeared ok.

    Any idea why and is there a way I could recover those previous data?

    Thank you.


    Merry Xmas & Happy New Year to all !!!!!

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Can the original varchar values be viewed as japanese, thai or chinese characters? In other words, if you picked one of each type of customer, and displayed them on your screen, what would you see? three languages, or all three in one language? I am concerned that the application may be storing these characters based on a client's local settings. This is not a problem, if the data is accessed only by machines with the same local settings, but add a new one into the mix....

    Is there any way to determine what language each name is meant to be stored in?

  3. #3
    Join Date
    Jan 2004
    Posts
    2
    I also have a similar problem.

    The database is set as US english codepage but is being written to by a webpage with Chinese encoding. The Chinese data is stored in the database as a string of characters, which if for example is copied to a html page and then encoded views fine. However I am now trying to move this chinese data into a Unicode database, which rather than displaying the chinese characters is showing the symbols exactly as they are in the database, even when veiwed using a utf8 webpage.

    Any help gratefully received!

    Aelfric

  4. #4
    Join Date
    Jun 2002
    Location
    Singapore
    Posts
    71

    varchar to nvarchar

    Originally posted by MCrowley
    Can the original varchar values be viewed as japanese, thai or chinese characters? In other words, if you picked one of each type of customer, and displayed them on your screen, what would you see? three languages, or all three in one language? I am concerned that the application may be storing these characters based on a client's local settings. This is not a problem, if the data is accessed only by machines with the same local settings, but add a new one into the mix....

    Is there any way to determine what language each name is meant to be stored in?
    I have done another test with my data field changed to nvarchar. Loaded a set of new data with customer names in Japanese, Thai and both Simplified & Traditional Chinese. I was able to see the foreign languages in their proper characters set. Meaning Japanese is Japanese, Thai is Thai and so on when I do a Return all rows of the table in SQL server Enterprise Manager.

    The properties of the SQL server shows SQL_Latin1_General_CP1_Cl_AS as the server collation.

    BUT when I do a select * statement in query analyzer, the foreign characters appeared in funny unreadable characters.

    Since showing Chinese is the highest priority in my project (more Chinese users), I was told by friends to change the code page (or server collation) to one that support Chinese so that Chinese could be displayed in query analyzer and proper data could also be brought into my web applications, Cognos Upfront.

    Any idea is the way to do it? What will happen to my other data? How will they be affected? I need advises on this before I could proceed.

    Thank you.

  5. #5
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    What font are you using for the results pane in Query Analyzer?

    (Tools->options->fonts tab

  6. #6
    Join Date
    Jun 2002
    Location
    Singapore
    Posts
    71
    Originally posted by MCrowley
    What font are you using for the results pane in Query Analyzer?

    (Tools->options->fonts tab
    I have tried so many fonts that I couldn't remember what they were but still unable to see proper characters shown.

    It's already 11:32pm now in Singapore and I am logging out now. Hope to hear from you again when I come back in the morning.

    Thanks a million.

  7. #7
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    As a curiosity, try a version of this query on one of the tables that includes chinese characters:

    select count(*)
    from authors
    where unicode(au_fname) > 255

    This will return the unicode value for the first character in each value fed into it. Hopefully, this will return a count. If not, you may be stuck with all 8-bit values in your nvarchar field, and the display of the characters is still dependent on the client.

  8. #8
    Join Date
    Jun 2002
    Location
    Singapore
    Posts
    71
    Originally posted by MCrowley
    As a curiosity, try a version of this query on one of the tables that includes chinese characters:

    select count(*)
    from authors
    where unicode(au_fname) > 255

    This will return the unicode value for the first character in each value fed into it. Hopefully, this will return a count. If not, you may be stuck with all 8-bit values in your nvarchar field, and the display of the characters is still dependent on the client.
    Thank you for your reply. Result returned 262

    select count(*)
    from customer
    where unicode(custname) > 255
    and salesorg='TW00'

    I have included one more condition to pick salesorg='TW00' (Taiwan) because my customer table has both English and foreign language (sales org of many countries) in the custname field.

    What's next?

    Thank you.

  9. #9
    Join Date
    Jun 2002
    Location
    Singapore
    Posts
    71
    Originally posted by MCrowley
    As a curiosity, try a version of this query on one of the tables that includes chinese characters:

    select count(*)
    from authors
    where unicode(au_fname) > 255

    This will return the unicode value for the first character in each value fed into it. Hopefully, this will return a count. If not, you may be stuck with all 8-bit values in your nvarchar field, and the display of the characters is still dependent on the client.
    Sorry, my mistake. I have been changing the fonts at the wrong place so the foreign languages characters didn't displayed properly. Instead of changing it for the Result Texts, I have been trying different fonts at the Editor. Well, I finally managed to see the characters shown properly but this has not resolved my orignal problem. I am still unable to send these foreign language characters to my external program, Cognos suite of products.


    ----------------------------------------------------------------------------------
    QUOTE FROM COGNOS SUPPORT :-

    Well, the first thing I need to know is whether the database is set to Unicode - nvarchar suggests it probably is. The only real alternative is to have the data in the correct code page (which precludes multiple languages) and to work from there.

    Currently, our products do not internally support Unicode (UTF-8). This being said, both PowerPlay and Impromptu support many character sets (Latin 1, 2 and 5, Greek, Hebrew, Shift-JIS [for Japanese], Big-5 [for traditional Chinese], GBK [for simplified Chinese], and KS-C [for Korean]).

    Depending on the way that data is stored inside the client's databases, it may be possible to "ask" the database to convert data before passing it to our products.
    ----------------------------------------------------------------------------------

    Base on the above quote from Cognos Support, does anyone has any advise how I could proceed from here? For example like changing the code page as suggested? How?

    Please advise. Thanks a million.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •