ORACLE判別欄位是否包含中文

標籤：itblog   lengthb   arc   sel   .net   個性   佔用   into   htm   

    在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

 

ORACLE判別欄位是否包含中文