In Italian language we have accent words like àèéìòù
They are quite important because for instance “is” is spelled
whereas “and” is spelled
Accent are bad guys even today: if you copy them from MS-Word inside an Oracle sql script you can end up with different UTF-8 values.
Also the accent are likely to be destroyed if you past them in your html page without using the correct html entity (i.e. è )
Sometimes you need to export the data inserted with accent: an Oracle Virtual column can be an elegant solution….if you have at least Oracle 11g.
Also a magic update can fix some nasty problems….here the solution:
ALTER TABLE TEST_TABLE ADD( NAME_NORMALIZED generated always AS ( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( ACCENT_COLUMN, 'à','a'''),'è','e'''),'é','e'''),'ì','i'''),'ò','o'''),'ù','u''') ));