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


前言

在日常处理数据的过程中,大家肯定会遇到很多奇奇怪怪的字符,然后还要对这些字符处理,比如你有个需求:识别字符串中的中文或是识别字母或是识别数字,甚至都识别出来然后剔除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 东方闪电sdf123123	 .·','sdf 东方闪电sdf123123	 .·');
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 3ADSDSasdsASDAS','1 2 3ASDasdsASDAS');
insert into test_tab values(10,'	1 2 3ADSDSasdsASDAS','	1 2 3ASDasdsASDAS');
insert into test_tab values(11,'sdf','sdf');
commit;

处理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;
/

处理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;
/
评论 8
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

赵延东的一亩三分地

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值