Re: How to identify unicode characters in record

Ana C. Dent wrote:
> 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!
Oracle Database Globalization Support Guide 10g Release 2 Appendix B: "UTF-8 Encoding
The UTF-8 character codes in Table B-2 show that the following conditions are true:
¦ ASCII characters use 1 byte
¦ European (except ASCII), Arabic, and Hebrew characters require 2 bytes
¦ Indic, Thai, Chinese, Japanese, and Korean characters as well as certain symbols
such as the euro symbol require 3 bytes

¦ Characters in the Private Use Area #1 require 3 bytes
¦ Supplementary characters require 4 bytes
¦ Characters in the Private Use Area #2 require 4 bytes"

Maybe the above can be of use. LENGTH indicates the number of characters in a VARCHAR2 column, LENGTHB indicates the number of bytes required to store the characters:
SELECT
  ID
FROM
  PART
WHERE
  LENGTH(DESCRIPTION)<>LENGTHB(DESCRIPTION);

Note: the VARCHAR2(320) column in the UTF-8 database may not be able to store 320 characters per row, even if those 320 characters could be stored in the same database with the WE8MSWIN1252 character set, if ASCII characters of 128 or above were used in the column.

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Thu Sep 07 2006 - 06:42:48 CDT