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!

how to translate/convert special characters , Need Help!!!!

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

  1. pgudur

    pgudur Active Member

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

    I appreciate if some one could help me on this.
    I have requirement to cleanup special character of field.
    The data which is retrieved from the field and written to text file.

    So Here
    i should replaced by characters Ã-

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

    Ã- this has written into file instead of i

    what would be cause for this?

    In database following values
    NLS_NCHAR_CHARACTERSET - AL16UTF16
    NLS_CHARACTERSET - UTF8

    I tried

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

    Can any one please provide me a solution.

    Thanks,
     
  2. sravanthi

    sravanthi Active Member

    Messages:
    6
    Likes Received:
    3
    Trophy Points:
    65
    Hi,

    Why not to use translate or replace function instead of convert.

    for ex:
    SELECT TRANSLATE('HÃ-re','Ã-','i') FROM dual;
    OR
    SELECT REPLACE('HÃ-re','Ã-','i') FROM dual;

    Regards,
    Sravanthi :D
     
    Sadik likes this.
  3. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,345
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You may be experiencing a problem between two different character schemes (ASCII and EBCDIC, for instance) where one character code does not translate to the other mapping. TRANSLATE would be a better option.