SQL> SELECT STR 2 FROM (SELECT '中国' STR FROM DUAL UNION ALL 3 SELECT '英国1' STR FROM DUAL UNION ALL 4 SELECT '美国2' STR FROM DUAL UNION ALL 5 SELECT '日本123' STR FROM DUAL UNION ALL 6 SELECT '韩国。,,.' STR FROM DUAL UNION ALL 7 SELECT '123' STR FROM DUAL UNION ALL 8 SELECT 'abcde' STR FROM DUAL) 9 WHERE TO_SINGLE_BYTE(STR) = TO_MULTI_BYTE(STR) 10 /
你的方法在NLS_CHARACTERSET=AL32UTF8 的确是可以的, 但是5楼的方法在us7ascii编码下是行不通的,测试如下:SQL> SELECT STR 2 FROM (SELECT '中国' STR FROM DUAL UNION ALL 3 SELECT '英国1' STR FROM DUAL UNION ALL 4 SELECT '美国2' STR FROM DUAL UNION ALL 5 SELECT '日本123' STR FROM DUAL UNION ALL 6 SELECT '韩国。,,.' STR FROM DUAL UNION ALL 7 SELECT '123' STR FROM DUAL UNION ALL 8 SELECT 'abcde' STR FROM DUAL) 9 WHERE TO_SINGLE_BYTE(STR) = TO_MULTI_BYTE(STR) ; STR ---------- 中国 英国1 美国2 日本123 韩国。,,. 123 abcde7 rows selected.SQL> SELECT * FROM nls_database_parameters WHERE parameter LIKE 'NLS_%';PARAMETER VALUE ------------------------------ ---------------------------------------- NLS_LANGUAGE AMERICAN NLS_NCHAR_CHARACTERSET AL16UTF16 NLS_TERRITORY AMERICA NLS_CURRENCY $ NLS_ISO_CURRENCY AMERICA NLS_NUMERIC_CHARACTERS ., NLS_CHARACTERSET US7ASCII NLS_CALENDAR GREGORIAN NLS_DATE_FORMAT DD-MON-RR NLS_DATE_LANGUAGE AMERICAN NLS_SORT BINARY NLS_TIME_FORMAT HH.MI.SSXFF AM NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR NLS_DUAL_CURRENCY $ NLS_COMP BINARY NLS_LENGTH_SEMANTICS BYTE NLS_NCHAR_CONV_EXCP FALSE NLS_RDBMS_VERSION 11.1.0.7.020 rows selected.SQL>
哇,,高手。。 小弟正在初学oracle select * from student where sname like '李__'; 怎么执行不了呀?
SQL> select * from v$nls_parameters where PARAMETER='NLS_CHARACTERSET';
PARAMETER VALUE
---------------------------------------------------------------- ----------------------------------------------------------------
NLS_CHARACTERSET US7ASCII
搞了几天,还是得用asciistr()函数,但是有的是繁体字,所以不好处理,比如:我匹配regexp_like(TRIM(name), '[a-z]')的时候,name为繁体字的也匹配到了,郁闷……
判断字符串中每个字符是否是双字节的,如果都是双字节字符,则表明字符串都是汉字。
详细的源码请看我的文章:
http://blog.csdn.net/xiexbb/archive/2009/06/25/4296629.aspx
select name from tb where length(name)*3=lengthb(name);
SELECT first_name, length(first_name), lengthb(first_name) FROM cnods.MEMBER WHERE rownum<10;
1 许燕 4 4
2 谢明明 6 6
3 秦岚 4 4
4 卢赞超 6 6
5 徐东 4 4
6 西村真友美 10 10
7 邹林江 6 6
8 chenxia 7 7
9 杨洪林 6 6
2 FROM (SELECT '中国' STR FROM DUAL UNION ALL
3 SELECT '英国1' STR FROM DUAL UNION ALL
4 SELECT '美国2' STR FROM DUAL UNION ALL
5 SELECT '日本123' STR FROM DUAL UNION ALL
6 SELECT '韩国。,,.' STR FROM DUAL UNION ALL
7 SELECT '123' STR FROM DUAL UNION ALL
8 SELECT 'abcde' STR FROM DUAL)
9 WHERE TO_SINGLE_BYTE(STR) = TO_MULTI_BYTE(STR)
10 /
STR
------------------------
中国
1 测试B类 4 7
2 待审批 3 6这是我的数据库出的结果.
应该更数据库的设置有关
这是我的数据库编码
如果使用UTF8的话 估计就如5楼所说了
但是5楼的方法在us7ascii编码下是行不通的,测试如下:SQL> SELECT STR
2 FROM (SELECT '中国' STR FROM DUAL UNION ALL
3 SELECT '英国1' STR FROM DUAL UNION ALL
4 SELECT '美国2' STR FROM DUAL UNION ALL
5 SELECT '日本123' STR FROM DUAL UNION ALL
6 SELECT '韩国。,,.' STR FROM DUAL UNION ALL
7 SELECT '123' STR FROM DUAL UNION ALL
8 SELECT 'abcde' STR FROM DUAL)
9 WHERE TO_SINGLE_BYTE(STR) = TO_MULTI_BYTE(STR) ; STR
----------
中国
英国1
美国2
日本123
韩国。,,.
123
abcde7 rows selected.SQL> SELECT * FROM nls_database_parameters WHERE parameter LIKE 'NLS_%';PARAMETER VALUE
------------------------------ ----------------------------------------
NLS_LANGUAGE AMERICAN
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
NLS_CHARACTERSET US7ASCII
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE AMERICAN
NLS_SORT BINARY
NLS_TIME_FORMAT HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY $
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE
NLS_RDBMS_VERSION 11.1.0.7.020 rows selected.SQL>
小弟正在初学oracle
select * from student where sname like '李__';
怎么执行不了呀?