Is there some sort of Oracle function that will either convert non-Ascii characters to equivalent Ascii characters or simply remove the non-Ascii characters? The particular situation I have is where an uploaded Excel sheet contains the em dash character and in Oracle this displays as an inverted question mark. I can illustrate it here:
select 'London—Paris' as original, ascii('—') badchar, Replace('London—Paris', chr(191), '--') fixed1, Replace('London—Paris', '—', '--') fixed2 from dual
This displays as:
Note: even though Oracle says the Ascii code for the em dash is 191, that's not really true, and that's why I don't really trust the "fixed1" result, even though it appears correct. The em dash is not part of the Ascii code, I believe it is a Unicode character.
It looks like I can fix it with the replace function, which is fine for this one particular case, but I'm wondering if there is a more global solution - either as a built-in Oracle function, or a user-written open source function/stored procedure/package that will clean up all non-Ascii characters in a given string/clob.