Re: How to identify unicode characters in record

From: Michel Cadot <micadot{at}altern{dot}org>
Date: Thu, 7 Sep 2006 06:40:32 +0200
Message-ID: <44ffa2c0$0$4772$626a54ce@news.free.fr>

"Ana C. Dent" <anacedent_at_hotmail.com> a écrit dans le message de news: Xns9836C5DB85016banspamoperamailcom_at_70.168.83.30...
| If I am having a good day, I can barely spell unicode.
| We are in the process of upgrading our application to support unicode
| characters.
| CREATE TABLE LOOKUP
| (ID NUMBER,
| DESCRIPTION VARCHAR2(320));
| This table exists in a 10GR2 database that supports UTF-8 character set.
|
| How do I query the databse to return all the IDs where DESCRIPTION contains
| 1 or more unicode (non-ASCII) characters?
|
| I am more than willing to RTFM, if you point me at which FM has the answer.
|
| Free clues would be much appreciated.
|
| TIA!
Knowing that:

1/ ascii code is from 0 to 127
2/ ascii and utf8 are identical on [0,127]
3/ if there is unicode and non-ascii code then at least on byte is greater than 128

Then I'd search for bytes greater than 128. Another trick is to use the ASCIISTR function and to search for "\" in the result. This function takes a string and converts it in ascii character replacing non ascii characters to their UCS2 code:

SQL> select asciistr('abcçdeéè') from dual; ASCIISTR('ABCÇDEÉÈ')

---

abc\00E7de\00E9\00E8

Regards
Michel Received on Wed Sep 06 2006 - 23:40:32 CDT