oracle中文ascii,Oracle 中文字符及中文乱码判断

Oracle里面如何识别存储字段里面是否存在中文？

方法一：

使用length和lengthb，如果中文的话length是一个字符，但是lengthb是两个字节。但是这个方法不适合数据库字符集非gbk的情况。

select * from t where length(c1) != lengthb(c1);

方法二：

使用asciistr函数得出是否字段里面包含“\”，因为当中文字符转换为ascii后，变成“\FFFD\FFFD”，但是需要注意一个特殊字符“\”，当它出现的时候转换后的码为“\005C”

SQL> select asciistr('/\)(^&*~中文字符') from dual;

ASCIISTR('/\)(^&*~中文字符')

-----------------------------------------------------------

/\005C)(

Oracle里面如何判断procedure等脚本里面是否包含中文乱码？

还是通过 asciistr 函数来实现了，如下：

SQL> select name, type

from user_source

where asciistr(text) like '%\FFFD%'

group by name, type;  2    3    4

NAME                   TYPE

---------------------- ------------

AGE_STAT_SUPERIOR      PROCEDURE

AUTOCREATE_SOURCE_DJ  PROCEDURE

AUTOCREATE__SOURCE_SJ  PROCEDURE

然后通过user_source加上上面的查询结果中的值，找得详细脚本

select text from user_source where name=upper('AGE_STAT_SUPERIOR');

附录：

The Oracle ASCIISTR Function

Definition:

The Oracle ASCIISTR function takes a string (or an expression that resolves to a string), in any and returns an ASCII version of the string in the current database character set.

The ASCIISTR function is useful for taking strings with accented characters (such as strings containing unicode characters) and converting them to an "ASCII-safe" format.

Example Usage:SELECT ASCIISTR('The letter Å will be converted.') asc_str

FROM dual;

This example returns the following string:The letter \00C5 will be converted.

Applies To:

Oracle 9i, Oracle 10g, Oracle 11g