1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Unicode character issue

Discussion in 'SQL PL/SQL' started by pgudur, Nov 5, 2010.

  1. pgudur

    pgudur Active Member

    Messages:
    18
    Likes Received:
    1
    Trophy Points:
    90
    Hi All,

    I appreciate if some one could help me on this.
    I have requirement to cleanup special character of field.

    So Here
    HÃ-re - I need value Hire(actual value) from the field.

    Ã- this has written into file instead of i

    In database following values are there
    NLS_NCHAR_CHARACTERSET - AL16UTF16
    NLS_CHARACTERSET - UTF8

    I tried below to achieve complete solution rather than using translate/replace.
    but not able to(its returning " ?? ").


    select convert('HÃ-re','UTF8') from dual
    ---------------
    Output: H??re

    Can any one please provide me a solution.

    Thanks,
    raman.:(
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    On the face of it you have an issue with a client operating system character set not matching the character set of the database and it not being a strict subset of the database characterset. Such configurations will likely result in data loss (such as you are experiencing) and convert() won't help you fix the issue.

    Basically, you simply need to perform an update of the column:

    Code (SQL):
     
    UPDATE ... SET ... = 'Hire' WHERE ... = 'HÃ-re';
     
    I know of no other method to correct this characterset 'mistranslation'.
     
  3. pgudur

    pgudur Active Member

    Messages:
    18
    Likes Received:
    1
    Trophy Points:
    90
    Thanks for the reply,
    I cannot update the data.
    we are writing data to text file by retrieving and here is the problem.

    Thanks...,
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You cannot convert the character codes generated by the mismatch of character sets between the client and the server, thus your choices are:

    1) Put the data into a temp table, update the bad values and generate your text file from the interim data
    2) Generate your text file with the bad characters

    As you no doubt know convert() does nothing useful to this string. You either find a way to update the data to generate a useful text file or generate your text file from the existing, unmodified data and live with the bad values.

    I would be investigating which client machine is generating these values and change the client's characterset to UTF-8 or to a characterset that is a strict subset of UTF-8.
     
  5. pgudur

    pgudur Active Member

    Messages:
    18
    Likes Received:
    1
    Trophy Points:
    90
    Yes, Its done now.
    Thanks all for the help and providing necessary information.
     
  6. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You are welcome.