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.csdn.net/yzsind/article/details/6106050
http://blog.itpub.net/9399028/viewspace-687789
About Me
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26736162/viewspace-1688209/，如需轉載，請註明出處，否則將追究法律責任。
