Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2004
    Posts
    1

    Unanswered: removing accents before insert/update

    Hi all!

    Could you help me solving this problem:

    I would like not to use accents when inserting/updating rows in my table.
    For example: when a user name is 'Jos', I wish to record simply 'Jose', that is, with no accent.
    Is there a way to configure Oracle NLS Parameters, so that it does not use accents and special characters () in varchar2 columns?


    Thanks in advance.
    --------------------

    Luciano Queiroz
    Brazil

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    I really wouldn't know how to do it through NLS parameters, but - for such purposes (regarding the fact I use Croatian and have a similar problem now and then) - I created a simple function that uses the TRANSLATE function, something like this:
    Code:
    CREATE OR REPLACE FUNCTION fun_trans_char (par_string IN CHAR)
       RETURN CHAR
    IS
    BEGIN
       RETURN TRANSLATE (par_string, '123', 'xyz');
    END;
    /
    
    Function created.
    
    Akon> select fun_trans_char('123456') translated_string from dual;
    
    TRANSLATED_STRING
    ------------------------------
    xyz456
    
    Akon_>
    Of course, instead of "123" in my example, you'd have characters you posted above, and "xyz" would be substituded with "regular" letters.

    P.S. Forgot to mention (but you've probably figured that out by yourself already) ... it is possible to put such a translation into the BEFORE INSERT/UPDATE trigger on your table(s). It is not an elegant solution to the problem - writing all those triggers all around and slow things down. Let's hope someone knows a better way to do it!
    Last edited by Littlefoot; 11-14-04 at 16:26.

Posting Permissions

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