【筆記】oracle 判斷欄位中的中文的方法

yellowlee發表於2009-03-12
筆記Oracle

判斷欄位中的中文的方法，與oracle的字符集有關，
 AL32UTF8字符集下：中文length() 的結果為3，ascii()的結果為8位，asciistr()可以判斷
  eg:
   select * from v$nls_parameters where parameter = 'NLS_CHARACTERSET';
    1 NLS_CHARACTERSET AL32UTF8
   select length('靠') from dual;
    1 1
   select lengthb('靠') from dual;
    1 3
   select asciistr('/\)(-=!@#$%^&*~中文字元') from dual;
    1 /\)(-=!@#$%^&*~\4E2D\6587\5B57\7B26
   select asciistr('\') from dual;
    1 \
   select ascii('哦') from dual;
    1 15045542
 ZHS16GBK字符集下：
   select * from v$nls_parameters where parameter = 'NLS_CHARACTERSET';
    1 NLS_CHARACTERSET ZHS16GBK
   select length('靠') from dual;
    1 1
   select lengthb('靠') from dual;
    1 2
   select asciistr('/\)(-=!@#$%^&*~中文字元') from dual;
    1 /\)(-=!@#$%^&*~\4E2D\6587\5B57\7B26
   select instr(asciistr('/)(-=!@#$%^&*~★ABC'),'\') from dual;
    1 15
   select asciistr('\') from dual;
    1 \
   select ascii('哦') from dual;
    1 50614

 可以自定義函式來實現一個對欄位值中是否包含有中文的判斷（如郵件地址驗證等）

  select instr(asciistr('/\)(-=!@#$%^&*~中文字元'),'\') from dual;
  如果instr的結果大於0，則包含中文或者一些特殊字元，為0則不含中文。
  (但是沒有能夠排除掉'\'的干擾，不能區別漢字和特殊字元)


比較好的方法(摘抄)：

從表裡提取漢字, 需要考慮字符集, 不同的字符集漢字的編碼有所不同
這裡以GB2312為例, 寫一函式準確地從表裡提取簡體漢字.

假設資料庫字符集編碼是GB2312, 環境變數(登錄檔或其它)的字符集也是GB2312編碼
並且儲存到表裡的漢字也都是GB2312編碼的

那麼也就是漢字是雙位元組的，且簡體漢字的編碼範圍是
B0A1 - F7FE
換算成10進位制就是
B0  A1    F7  FE
176,161 - 247,254

我們先看一下asciistr函式的定義
Non-ASCII characters are converted to the form. \xxxx, where xxxx represents a UTF-16 code unit.
但是這並不表示以 "\" 開始的字元就是漢字了

舉例如下
SQL> select * from test;

NAME
--------------------
,啊OO10哈
你好aa
大家好aa/
☆大海123
★ABC

這裡第5條記錄有一個實心的五角星
然後用asciistr函式轉換一下試試
SQL> select name,asciistr(name) from test;

NAME                 ASCIISTR(NAME)
-------------------- ----------------------
,啊OO10哈            ,\554AOO10\54C8
你好aa               \4F60\597Daa
大家好aa/            \5927\5BB6\597Daa/
☆大海123            \2606\5927\6D77123
★ABC                \2605ABC

我們看到最後一條記錄的實心五角星也是 "\"開頭的
此時我們就不能用asciistr(欄位)是否存在 "\" 來判斷是否含有漢字了.

我的函式如下，基本思路是判斷字元的編碼是否在GB2312規定的漢字編碼範圍之內
--------------------------------------------------------------------------------
create or replace function get_chinese(p_name in varchar2) return varchar2 as
  v_code    varchar2(30000) := '';
  v_chinese varchar2(4000) := '';
  v_comma   pls_integer;
  v_code_q  pls_integer;
  v_code_w  pls_integer;
begin
  if p_name is not null then
    select replace(substrb(dump(p_name, 1010),
                           instrb(dump(p_name, 1010), 'ZHS16GBK:')),
                   'ZHS16GBK: ',
                   '')
      into v_code
      from dual
     where rownum = 1;
    for i in 1 .. length(p_name) loop
      if lengthb(substr(p_name, i, 1)) = 2 then
        v_comma  := instrb(v_code, ',');
        v_code_q := to_number(substrb(v_code, 1, v_comma - 1));
        v_code_w := to_number(substrb(v_code,
                                      v_comma + 1,
                                      abs(instrb(v_code, ',', 1, 2) -
                                          v_comma - 1)));
        if v_code_q >= 176 and v_code_q <= 247 and v_code_w >= 161 and
           v_code_w <= 254 then
          v_chinese := v_chinese || substr(p_name, i, 1);
        end if;
        v_code := ltrim(v_code, '1234567890');
        v_code := ltrim(v_code, ',');
      end if;
      v_code := ltrim(v_code, '1234567890');
      v_code := ltrim(v_code, ',');
    end loop;
    return v_chinese;
  else
    return '';
  end if;
end;
/
--------------------------------------------------------------------------------
好，現在來執行一些語句
SQL> select * from test;

NAME
--------------------
,啊OO10哈
你好aa
大家好aa/
☆大海123
★ABC

5 rows selected.

1. 列出有漢字的記錄
SQL> select name from test where length(get_chinese(name))>0;

NAME
--------------------
,啊OO10哈
你好aa
大家好aa/
☆大海123

4 rows selected.

2. 列出有漢字的記錄，並且只列出漢字

SQL> select get_chinese(name) from test where length(get_chinese(name))>0;

GET_CHINESE(NAME)
---------------------------------------------------------------------------
啊哈
你好
大家好
大海

4 rows selected.


需要說明的是GB2312共有6763個漢字，即72*94-5=6763
我這裡是計算72*94，沒有減去那5個，那五個是空的。等查到了再減去
============

改寫這個函式，可以提取非漢字或者漢字
該函式有兩個引數，第一個表示要提取的字串，第二個是1，表示提取漢字，是非1，表示提取非漢字
--------------------------------------------------------------------------------
create or replace function get_chinese(p_name    in varchar2,
                                       p_chinese in varchar2)
  return varchar2 as
  v_code        varchar2(30000) := '';
  v_chinese     varchar2(4000) := '';
  v_non_chinese varchar2(4000) := '';
  v_comma       pls_integer;
  v_code_q      pls_integer;
  v_code_w      pls_integer;
begin
  if p_name is not null then
    select replace(substrb(dump(p_name, 1010),
                           instrb(dump(p_name, 1010), 'ZHS16GBK:')),
                   'ZHS16GBK: ',
                   '')
      into v_code
      from dual
     where rownum = 1;
    for i in 1 .. length(p_name) loop
      if lengthb(substr(p_name, i, 1)) = 2 then
        v_comma  := instrb(v_code, ',');
        v_code_q := to_number(substrb(v_code, 1, v_comma - 1));
        v_code_w := to_number(substrb(v_code,
                                      v_comma + 1,
                                      abs(instrb(v_code, ',', 1, 2) -
                                          v_comma - 1)));
        if v_code_q >= 176 and v_code_q <= 247 and v_code_w >= 161 and
           v_code_w <= 254 then
          v_chinese := v_chinese || substr(p_name, i, 1);
        else
          v_non_chinese := v_non_chinese || substr(p_name, i, 1);
        end if;
        v_code := ltrim(v_code, '1234567890');
        v_code := ltrim(v_code, ',');
      else
        v_non_chinese := v_non_chinese || substr(p_name, i, 1);
      end if;
      v_code := ltrim(v_code, '1234567890');
      v_code := ltrim(v_code, ',');
    end loop;
    if p_chinese = '1' then
      return v_chinese;
    else
      return v_non_chinese;
    end if;
  else
    return '';
  end if;
end;


/--------------------------------------------------------------------------------
SQL> select * from a;

NAME
--------------------
我們啊、
他（艾呀）是★們
他的\啊@

SQL> select get_chinese(name,1) from a;

GET_CHINESE(NAME,1)
-----------------------------------------
我們啊
他艾呀是們
他的啊

SQL> select get_chinese(name,0) from a;

GET_CHINESE(NAME,0)
-----------------------------------------
、
（）★
\@

SQL>

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/16179598/viewspace-566934/，如需轉載，請註明出處，否則將追究法律責任。

相關文章