Italian Accent Oracle fix

calendar Feb 10, 2017 · 1 min read  en sql  · oracle relax  ·
Share on: linkedin copy

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]