Oracle 12.1
I need to convert business names from around the world from UTF8 to ASCII. These names have non ASCII characters specific the the alphabet of the language. If there is a simple Java solution then that also would be OK, but I'd like to try to stay in SQL and PL/SQL if I can.
This code below seems to convert 100% of characters correctly, but lowercases everything. Using binary_ci also lower cases everything since _ci stands for case insensitive. Is there a way to use nlssort with a different NLS_SORT that does not lowercase everything?
- trim(rtrim(utl_raw.cast_to_varchar2((nlssort(p_name, 'NLS_SORT=binary_ai'))), chr(0)))
convert() works about 95%. This does not work for Turkish and does not translate all characters of some languages. For instance it converts 'ß' to a ?. When I convert 'ß' to ss first with a simple replace and then do a convert it converts ss to ?. Why does it do that?
- CONVERT(p_name, 'US7ASCII', 'AL32UTF8')
Is there a open source package somewhere? Like I said Java would be OK solution.