Link to home
Start Free TrialLog in
Avatar of John Wilkinson
John WilkinsonFlag for United States of America

asked on

Clean up non-Ascii characters in Oracle SQL

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:

    'London—Paris' as original,  
    ascii('—') badchar,
    Replace('London—Paris', chr(191), '--') fixed1,
    Replace('London—Paris', '—', '--') fixed2
from dual

Open in new window

This displays as:

User generated image

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.

Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial