oracle中如何判断一个字符串是否含有汉字
1.1 BLOG文档结构图
1.2 前言部分
1.2.1 导读
各位技术爱好者，看完本文后，你可以掌握如下的技能，也可以学到一些其它你所不知道的知识，~O(∩_∩)O~：
①全角字符的判断，或者是含有汉字的字符串的判断
本文如有错误或不完善的地方请大家多多指正，ITPUB留言或QQ皆可，您的批评指正是我写作的最大动力。
1.2.2 实验环境介绍
11.2.0.3 RHEL6.5
1.2.3 本文简介
看到网友问，怎么查询表中某个字段数据是不是包含了全角字符啊？ 这个问题涉及到几个个函数：to_single_byte、length和lengthb，我之前做开发的时候研究的是如何判断一个字符串中是否包含中文，其实和这个本质是一样的，且看实验部分。
1.3 实验部分
1.3.1 lengthb和length函数结合to_single_byte函数
---含有汉字，严格的说是含有全角字符
SELECT l.name,
length(l.name),
lengthb(l.name)
FROM xb_link l
WHERE length(l.name) != lengthb(l.name)
AND length(l.name) < 20;
以下数据也满足条件：
SELECT l.id,
l.name
FROM xb_link l
WHERE length(l.name) != lengthb(l.name)
AND l.metacategory IN
('com.gxlu.ngrm.network.DDNCircuit',
'com.gxlu.ngrm.network.FRCircuit',
'com.gxlu.ngrm.network.ATMCircuit',
'com.gxlu.ngrm.network.DDNOCircuit',
'com.gxlu.ngrm.network.FROCircuit')
AND l.id IN ('301898331', '301898335', '301908187', '301929403');
所以可以借助to_single_byte函数来解决。
SELECT l.id,
l.name,
to_single_byte(l.name),
length(l.name) l1,
lengthb(l.name) l2,
length(to_single_byte(l.name)) l
FROM xb_link l
WHERE length(l.name) != lengthb(l.name)
AND l.metacategory IN
('com.gxlu.ngrm.network.DDNCircuit',
'com.gxlu.ngrm.network.FRCircuit',
'com.gxlu.ngrm.network.ATMCircuit',
'com.gxlu.ngrm.network.DDNOCircuit',
'com.gxlu.ngrm.network.FROCircuit')
AND l.id IN ('301898331', '301898335', '301908187', '301929403');
1.3.2 regexp_replace --替换其它字符为’’
SELECT
l.id,
l.name
FROM xb_link l
WHERE regexp_replace(TRIM(l.name),
'([A-Za-z0-9]|[[:punct:]]|[[:space:]])',
'') IS NOT NULL;
1.3.3 to_multi_byte 函数--全是全角字符
---全是汉字
SELECT l.name,
to_multi_byte(l.name)
FROM xb_link l
WHERE l.name = to_multi_byte(l.name);
SELECT l.name,
to_multi_byte(l.name)
FROM xb_link l;
1.3.4 网友问题解答：某个字段数据是不是包含了全角字符？
drop table aa;
create table aa (col varchar2(255));
SELECT * FROM aa for update;
SELECT a.col,
to_single_byte(a.col),
length(a.col),
lengthb(a.col),
length(to_single_byte(a.col)),
lengthb(to_single_byte(a.col))
FROM aa a
WHERE (lengthb(a.col) - length(a.col)) <> (lengthb(to_single_byte(a.col)) - length(to_single_byte(a.col)));
1.4 总结
方法很简单，网友可能还有其他的办法，欢迎留言，对于不同的场景处理方式有很多种，我们应该学会灵活变通。
可以利用LENGTH、LENGTHB和TO_SINGLE_BYTE函数来解决这个问题。其中，LENGTH返回以字符为单位的长度，LENGTHB返回以字节为单位的长度，TO_SINGLE_BYTE将字符串中的多字节字符转化为单字节字符。此外，还可以使用ASCIISTR和CONVERT函数找出包含汉字的字符串。若利用LENGTH、LENGTHB和TO_SINGLE_BYTE函数来实现该需求，则类似的WHERE条件为：“LENGTHB(COL) <> LENGTH(COL) AND LENGTHB(TO_SINGLE_BYTE(COL)) <> LENGTH(TO_SINGLE_BYTE(COL))”。
下面给出一个示例，在AA表中插入的“２”是全角字符。
SYS@lhrdb> CREATE TABLE AA (COL VARCHAR2(255));
Table created.
SYS@lhrdb> INSERT INTO AA (COL) VALUES ('1');
1 row created.
SYS@lhrdb> INSERT INTO AA (COL) VALUES ('２'); --全角字符
1 row created.
SYS@lhrdb> INSERT INTO AA (COL) VALUES ('小麦苗');
1 row created.
SYS@lhrdb> COMMIT;
Commit complete.
SYS@lhrdb> SELECT * FROM AA;
COL
----------
1
２
小麦苗
SYS@lhrdb> COL COL FORMAT A10
SYS@lhrdb> COL SINGLE_COL FORMAT A10
SYS@lhrdb> SELECT A.COL COL,
2 TO_SINGLE_BYTE(A.COL) SINGLE_COL,
3 LENGTH(A.COL) LENGTH_COL,
4 LENGTHB(A.COL) LENGTHB_COL,
5 LENGTH(TO_SINGLE_BYTE(A.COL)) SINGLE_LENGTH_COL,
6 LENGTHB(TO_SINGLE_BYTE(A.COL)) SINGLE_LENGTHB_COL
7 FROM AA A
8 WHERE LENGTHB(A.COL) <> LENGTH(A.COL)
9 AND LENGTHB(TO_SINGLE_BYTE(A.COL)) <> LENGTH(TO_SINGLE_BYTE(A.COL));
COL SINGLE_COL LENGTH_COL LENGTHB_COL SINGLE_LENGTH_COL SINGLE_LENGTHB_COL
---------- ---------- ---------- ----------- ----------------- ------------------
小麦苗 小麦苗 3 6 3 6
2015-01-19 14:26 by 潇湘隐者,
8553 阅读,
评论,
收藏,
编辑
在ORACLE数据库中如何查找那些字段里面包含中文的数据记录呢，有时候就是有这样的特殊需求，下面整理了一些判别字段中包含中文记录的几个方法
1：使用ASCIISTR函数判别
ASCIISTR函数说明：
ASCIISTR返回字符的ASCII形式的字符串。非ASCII的字符被转化为\xxxx的形式。
使用ASCIISTR函数也是根据非ASCII字符会被转化这个特性来判别中文字符，只要里面包含中文字符，则必定会有\xxx这样的字符。且简体汉字的编码范围是B0A1 - F7FE.如下例子所示
CREATE TABLE TEST
(
NAME_ONE CHAR(24)
,NAME_TWO VARCHAR2(24)
,NAME_THR NCHAR(24)
,NAME_FOR NVARCHAR2(24)
)
INSERT INTO TEST
SELECT 'abc10', 'abc20', 'abc30', 'abc40' FROM DUAL UNION ALL
SELECT 'abc11', 'abc21', 'abc31', 'abc41' FROM DUAL UNION ALL
SELECT 'abc12', 'abc22', 'abc32', 'abc42' FROM DUAL UNION ALL
SELECT 'abc1!', 'abc2!', 'abc3!', 'abc4!' FROM DUAL UNION ALL
SELECT 'abc1#', 'abc2#', 'abc3#', 'abc4#' FROM DUAL UNION ALL
SELECT 'abc1$', 'abc2$', 'abc3$', 'abc4$' FROM DUAL UNION ALL
SELECT 'ab测试1', 'ab测试2', 'ab测试3', 'ab测试4' FROM DUAL;
使用 ASCIISTR(NAME_ONE) LIKE '%\%' 就能判别那些有中文的记录。如下所示：
SELECT NAME_ONE FROM TEST WHERE ASCIISTR(NAME_ONE) LIKE '%\%'
但是如果字段里面的非ASCII字符不仅仅有中文，例如还有日文之类，那么这个方法就不能准确判别了，如下所示，我插入一条包含日文的记录.
INSERT INTO TEST
SELECT 'abこんにちは1', 'abこんにちは2', 'abこんにちは3', 'abこんにちは4' FROM DUAL;
COMMIT;
SQL> SELECT NAME_ONE FROM TEST WHERE ASCIISTR(NAME_ONE) LIKE '%\%';
NAME_ONE
------------------------
ab测试1
abこんにちは1
2：使用CONVERT函数判别
CONVERT函数说明：
CONVERT(inputstring,dest_charset,source_charset)
inputstring：要转换的字符串
dest_charset：目标字符集
source_charset：原字符集
SQL> SELECT * FROM NLS_DATABASE_PARAMETERS WHERE PARAMETER='NLS_CHARACTERSET';
PARAMETER VALUE
------------------------------ ----------------------------------------
NLS_CHARACTERSET UTF8
SQL> SELECT NAME_ONE, NAME_TWO FROM TEST WHERE NAME_ONE <> CONVERT(NAME_ONE, 'ZHS16GBK', 'UTF8');
NAME_ONE NAME_TWO
------------------------ ------------------------
ab测试1 ab测试2
abこんにちは1 abこんにちは2
3:使用函数length和lengthb来判别
使用函数length与lengthb来判别，是基于中文字符占用2~4个字节，而ASCII字符占用一个字节，那么对比LENGTH与LENGTHB就会不一样。这样就能判别字段中是否包含中文字符，但是跟ASCIISTR一样，如果里面的非ASCI字符包含非中文，它一样不能判别。依然有取巧嫌疑。
SELECT NAME_ONE FROM TEST WHERE LENGTH(NAME_ONE) != LENGTHB(NAME_ONE);
关于三者的性能，基本上都差不多，并没有那个性能要快一点。当然我没有详细、大量测试过，只是就某个案例的执行计划分析而已。
参考资料：
http://www.bitscn.com/pdb/oracle/201407/240540.html
http://blog.youkuaiyun.com/yzsind/article/details/6106050
http://blog.itpub.net/9399028/viewspace-687789
About Me
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26736162/viewspace-1688209/，如需转载，请注明出处，否则将追究法律责任。
本文介绍了在Oracle数据库中如何通过lengthb、length、to_single_byte等函数判断字符串是否包含汉字，以及regexp_replace和to_multi_byte的使用方法。实验部分提供了具体的SQL查询示例，帮助理解不同方法的实现和应用场景。
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
