Home » SQL & PL/SQL » SQL & PL/SQL » Using CONVERT US7ASCII to drop accents
icon5.gif  Using CONVERT US7ASCII to drop accents [message #240286] Thu, 24 May 2007 09:41 Go to next message
PunchMonkey
Messages: 5
Registered: March 2007
Junior Member
Hi folks, I'm looking for a method to quickly convert a string to 7 bit ASCII. The CONVERT function seems promising, but it doesn't seem to catch the following characters:

The environment here is Oracle 9.2.0.1 on Windows 2003 and Oracle 10.2.0.3 on Windows 2003.

SQL> SELECT CONVERT ('', 'US7ASCII') FROM dual;

CONVERT('
--------------------------------
Cueaaa?ceeeiiiA?E??ooouuOUaiou??


Do I have the wrong approach? How can I get these other characters mapped?

[Updated on: Thu, 24 May 2007 09:52]

Report message to a moderator

Re: Using CONVERT US7ASCII to drop accents [message #240287 is a reply to message #240286] Thu, 24 May 2007 09:43 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
perhaps the ASCIISTR function
Re: Using CONVERT US7ASCII to drop accents [message #240290 is a reply to message #240287] Thu, 24 May 2007 09:51 Go to previous messageGo to next message
PunchMonkey
Messages: 5
Registered: March 2007
Junior Member
Using ASCIISTR returns a lot of hex codes. Looking up the oracle docs on this it sounds like this is because the data in the database is stored in non-ascii (WE8MSWIN1252 in my case here).

SQL> SELECT ASCIISTR('') from dual;

ASCIISTR('')
--------------------------------------------------------------------------------
\20AC\0081\201A\0192\201E\2026\2020\2021\02C6\2030\0160\2039\0152\008D\017D\008F
\0090\2018\2019\201C\201D\2022\2013\2014\2122\0161\00A0\00A1\00A2\00A3\00A4\00A5

Re: Using CONVERT US7ASCII to drop accents [message #240301 is a reply to message #240286] Thu, 24 May 2007 10:16 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
ASCII is a 7-bit character representation.
EXACTLY what do you want done to "characters" which are single byte values between 128 & 255 (decimal)?
What do you want done with characters which are represented in 2 bytes?
Enquiring minds want to know.
Re: Using CONVERT US7ASCII to drop accents [message #240309 is a reply to message #240286] Thu, 24 May 2007 10:30 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Fonction converts the unknown characters in the target character set following some conversion rules.
For instance, "" that does not exist in US7ASCII is converted to "a".
But the conversion table does not contain all possible rules and when there is none (for instance for "") it converts to a "replacement character" a character defined to replace unknown and unconvertible character. In this case the replacement character is "?".

Now you can manually translate them before:
SQL> select convert(translate('','','aAoonN'),'US7ASCII') FROM dual;
CONVERT(TRANSLATE('
------------------------------
CueaaaaceeeiiiAAEooouuOUaiounN

1 row selected.

Regards
Michel
Re: Using CONVERT US7ASCII to drop accents [message #240332 is a reply to message #240309] Thu, 24 May 2007 11:21 Go to previous messageGo to next message
PunchMonkey
Messages: 5
Registered: March 2007
Junior Member
Thanks Michel, that sounds like a good idea and I think we'll try this out.

I'm still confused as to why gets converted to an a, but doesn't. But it's not a mystery I'll lose sleep over Smile

To anacedent, we have a list of phrases that are already devoid of accents. We need to pull data out of Oracle and match it against this list. Ideally, we'd like to convert the characters as we pull them out of the database.

- PM
Re: Using CONVERT US7ASCII to drop accents [message #240335 is a reply to message #240332] Thu, 24 May 2007 11:30 Go to previous message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I'm still confused as to why gets converted to an a, but doesn't

Someone in an Oracle development team forgot the character in the conversion table. Mad

Regards
Michel
Previous Topic: START WITH/CONNECT BY returns fewer rows in 10g than 9i
Next Topic: granting system privs to a role then user but nothing shows up
Goto Forum:
  


Current Time: Thu Dec 08 08:17:16 CST 2016

Total time taken to generate the page: 0.10234 seconds