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)(-=!@#$%^&*~\4E2D\6587\5B57\7B26
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
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/9399028/viewspace-687789/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/9399028/viewspace-687789/