通过Oracle识别字符串中的中文or字母or数字来介绍全角半角转换函数(to_multi_byte/to_single_byte)在varchar/clob中的使用案例_oracle 字符转小数

前言

在日常处理数据的过程中，大家肯定会遇到很多奇奇怪怪的字符，然后还要对这些字符处理，比如你有个需求：识别字符串中的中文或是识别字母或是识别数字，甚至都识别出来然后剔除or保留某些字符汉字或数字。
你去百度了一下相关问题，然后得到的结果大都是用正则 ‘\4E00’ and '\9FA5’来识别中文范围用a-zA-z或0-9或[:digit:][:alpha:]来识别字母或数字。但是如果你的字符串中包含全角字符，那这样是识别不全的！！！那怎么做才能够正确的识别中文、字母、数字呢？？？那就要考虑先做全半角的转换了，Oracle中用全角半角转换函数(to_multi_byte/to_single_byte)来实现，但是对于varchar/clob处理方式还不一样，下面是我实现识别字符串中的中文或是识别字母或是识别数字需求的方式。

一、全角半角转换函数(to_multi_byte/to_single_byte)官方介绍

开始之前先上两个函数在官方得详细说明，因为之前开发函数之前没去官方看说明，直接百度看说明导致得坑，下面是官方文档得截图，官方明确说明这两个函数不支持clob字段处理，但是我们可以通过隐式转换来实现，后面给出了本人写的对正常情况下varchar类型的支持以及相同功能下clob字段的实现方法：

Purpose

TO_SINGLE_BYTE returns char with all of its multibyte characters
converted to their corresponding single-byte characters. char can be
of data type CHAR, VARCHAR2, NCHAR, or NVARCHAR2. The value returned
is in the same data type as char.

Any multibyte characters in char that have no single-byte equivalents
appear in the output as multibyte characters. This function is useful
only if your database character set contains both single-byte and
multibyte characters.

This function does not support CLOB data directly. However, CLOBs can
be passed in as arguments through implicit data conversion.

See Also:

“Data Type Comparison Rules” for more information.

Appendix C in Oracle Database Globalization Support Guide for the
collation derivation rules, which define the collation assigned to the
character return value of TO_SINGLE_BYTE

Purpose

TO_MULTI_BYTE returns char with all of its single-byte characters
converted to their corresponding multibyte characters. char can be of
data type CHAR, VARCHAR2, NCHAR, or NVARCHAR2. The value returned is
in the same data type as char.

Any single-byte characters in char that have no multibyte equivalents
appear in the output string as single-byte characters. This function
is useful only if your database character set contains both
single-byte and multibyte characters.

This function does not support CLOB data directly. However, CLOBs can
be passed in as arguments through implicit data conversion.

See Also:

“Data Type Comparison Rules” for more information.

Appendix C in Oracle Database Globalization Support Guide for the
collation derivation rules, which define the collation assigned to the
character return value of TO_MULTI_BYTE

二、识别字符串中的中文、英文、数字

测试数据如下：

------测试数据：
create table test_tab(id number,name varchar2(1000),loc clob);
insert into test_tab values(1,'','');
insert into test_tab values(2,'	　，．·','	　．，·');
insert into test_tab values(3,' '||chr(10)||chr(13),' '||chr(10)||chr(13));
insert into test_tab values(4,'sdf 东方闪电ｓｄｆ１２３123	　．·','sdf 东方闪电ｓｄｆ１２３123	　．·');
insert into test_tab values(5,'123','123');
insert into test_tab values(6,' 1 2 3',' 1 2 3');
insert into test_tab values(7,'1 2 3','1 2 3');
insert into test_tab values(8,'1 2 3ADSDSａｓｄｓＡＳＤＡＳ','1 2 3ASDａｓｄｓＡＳＤＡＳ');
insert into test_tab values(10,'	1 2 3ADSDSａｓｄｓＡＳＤＡＳ','	1 2 3ASDａｓｄｓＡＳＤＡＳ');
insert into test_tab values(11,'ｓｄｆ','ｓｄｆ');
commit;
1
2
3
4
5
6
7
8
9
10
11
12
13

处理varchar类型数据

create or replace function trans_varchar(p_char varchar2) return varchar2 DETERMINISTIC is
  v_cache varchar2(4000);
  v_char varchar2(4000):=to_single_byte(p_char);
  cursor col is
    select regexp_substr(v_char, '[^[:digit:],^[:punct:],^[:cntrl:],^[:blank:]]', 1, level) as ch,
           regexp_substr(v_char, '[[:digit:]]', 1, level) as nu
      from dual
       connect by level <= case when length(p_char)>15 then 15 else length(p_char) end;
begin
  v_cache := '';
  for i in col loop
    case
      when asciistr(i.ch) between '\4E00' and '\9FA5' then
        v_cache := v_cache || i.ch;
        /* when  i.ch between 0 and 9 then 
        v_cache:=v_cache||i.ch;*/
      when i.ch between 'a' and 'z' then
        v_cache := v_cache || i.ch;
      when i.ch between 'A' and 'Z' then
        v_cache := v_cache || i.ch;
      else
        v_cache := v_cache || i.nu;
    end case;
  
  end loop;
  if v_cache is null then
    return null;
  else
    return rtrim(ltrim(rtrim(ltrim(rtrim(ltrim(trim(v_char),
                                               chr(9)),
                                         chr(9)),
                                   chr(10)),
                             chr(10)),
                       chr(13)),
                 chr(13));
  end if;
end trans_varchar;
/
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39

处理clob类型数据

create or replace function trans_clob(p_char clob) return clob DETERMINISTIC is
  v_cache varchar2(4000);
   v_char clob:=to_single_byte(dbms_lob.substr(p_char));
  cursor col is
   select regexp_substr(v_char, '[^[:digit:],^[:punct:],^[:cntrl:],^[:blank:]]', 1, level) as ch,
           regexp_substr(v_char, '[[:digit:]]', 1, level) as nu
      from dual
    connect by level <= 15;
begin
  v_cache := '';
  for i in col loop
    case
      when asciistr(i.ch) between '\4E00' and '\9FA5' then
        v_cache := v_cache || i.ch;
        /* when  i.ch between 0 and 9 then
        v_cache:=v_cache||i.ch;*/
      when i.ch between 'a' and 'z' then
        v_cache := v_cache || i.ch;
      when i.ch between 'A' and 'Z' then
        v_cache := v_cache || i.ch;
      else
        v_cache := v_cache || i.nu;
    end case;
  end loop;
  if v_cache is null then
    return null;
  else
    return rtrim(ltrim(rtrim(ltrim(rtrim(ltrim(trim(v_char),
                                               chr(9)),
                                         chr(9)),
                                   chr(10)),
                             chr(10)),
                       chr(13)),
                 chr(13));
  end if;
end trans_clob;
/
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37