Avatar of bassman592
bassman592
Flag 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:


select
    '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:


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.



SQL* Oracle PL/SQL* Unicode

Avatar of undefined
Last Comment
slightwv (䄆 Netminder)

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
slightwv (䄆 Netminder)

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Your help has saved me hundreds of hours of internet surfing.
fblack61