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:

 

[sql]
ALTER TABLE TEST_TABLE ADD( NAME_NORMALIZED generated
always
AS
( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE(
ACCENT_COLUMN,
‘à’,’a”’),’è’,’e”’),’é’,’e”’),’ì’,’i”’),’ò’,’o”’),’ù’,’u”’) ));
[/sql]