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

简介: 在日常处理数据的过程中,大家肯定会遇到很多奇奇怪怪的字符,然后还要对这些字符处理,比如***你有个需求:识别字符串中的中文或是识别字母或是识别数字,甚至都识别出来然后剔除or保留某些字符汉字或数字***。你去百度了一下相关问题,然后得到的结果大都是用正则 '\4E00' and '\9FA5'来识别中文范围用a-zA-z或0-9或[:digit:][:alpha:]来识别字母或数字。但是如果你的字符串中包含全角字符,那这样是识别不全的!!!那怎么做才能够正确的识别中文、字母、数字呢???那就要考虑先做全半

前言

在日常处理数据的过程中,大家肯定会遇到很多奇奇怪怪的字符,然后还要对这些字符处理,比如你有个需求:识别字符串中的中文或是识别字母或是识别数字,甚至都识别出来然后剔除or保留某些字符汉字或数字
你去百度了一下相关问题,然后得到的结果大都是用正则 '\4E00' and '\9FA5'来识别中文范围用a-zA-z或0-9或:digit:来识别字母或数字。但是如果你的字符串中包含全角字符,那这样是识别不全的!!!那怎么做才能够正确的识别中文、字母、数字呢???那就要考虑先做全半角的转换了,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;
/
相关文章
|
1月前
|
JavaScript 前端开发 Java
struts+hibernate+oracle+easyui实现lazyout组件的简单案例——Jsp页面
struts+hibernate+oracle+easyui实现lazyout组件的简单案例——Jsp页面
9 0
|
1月前
struts+hibernate+oracle+easyui实现lazyout组件的简单案例——OpSessionview实现
struts+hibernate+oracle+easyui实现lazyout组件的简单案例——OpSessionview实现
12 0
|
1月前
struts+hibernate+oracle+easyui实现lazyout组件的简单案例——EmpDao层代码
struts+hibernate+oracle+easyui实现lazyout组件的简单案例——EmpDao层代码
16 0
|
2月前
|
SQL Oracle 关系型数据库
Oracle查询优化-查询只包含数字或字母的数据
【2月更文挑战第4天】【2月更文挑战第10篇】查询只包含数字或字母的数据
88 1
|
1月前
|
Java
struts+hibernate+oracle+easyui实现lazyout组件的简单案例——Action的实现类
struts+hibernate+oracle+easyui实现lazyout组件的简单案例——Action的实现类
10 0
|
1月前
struts+hibernate+oracle+easyui实现lazyout组件的简单案例——工具类
struts+hibernate+oracle+easyui实现lazyout组件的简单案例——工具类
10 0
|
1月前
struts+hibernate+oracle+easyui实现lazyout组件的简单案例——DeptDao层代码
struts+hibernate+oracle+easyui实现lazyout组件的简单案例——DeptDao层代码
9 0
|
1月前
|
XML Java 数据库连接
struts+hibernate+oracle+easyui实现lazyout组件的简单案例——hibernate的config文件(hibernate.cfg.xml)
struts+hibernate+oracle+easyui实现lazyout组件的简单案例——hibernate的config文件(hibernate.cfg.xml)
11 0
|
1月前
struts+hibernate+oracle+easyui实现lazyout组件的简单案例——Dept实体类和对应的配置信息
struts+hibernate+oracle+easyui实现lazyout组件的简单案例——Dept实体类和对应的配置信息
13 1
|
4天前
|
Oracle 算法 关系型数据库
Oracle常用系统函数之数字类函数:数字的魔术师
【4月更文挑战第19天】Oracle数据库中的数字类函数是数字处理的魔术师,包括`ROUND`(四舍五入),`CEIL`和`FLOOR`(向上/下取整),以及`ABS`(计算绝对值)。还有`MOD`、`TRUNC`和`POWER`等函数,提供求余数、截断和计算幂的功能。熟练运用这些函数能提升数据管理效率,让处理数字变得更简单、有趣。

推荐镜像

更多