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?
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:
CREATE OR REPLACE FUNCTION fun_trans_char (par_string IN CHAR)
RETURN TRANSLATE (par_string, '123', 'xyz');
Akon> select fun_trans_char('123456') translated_string from dual;
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!