Italian Accent Oracle fix

In Italian language we have accent words like àèéìòù

They are quite important because for instance “is” is spelled

è

whereas “and” is spelled

e

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''') ));

Leave a Reply