oracle正则表达式regexp_like的用法详解

男娘i 2021-06-26 16:06 351阅读 0赞

`ORACLE`中的支持正则表达式的函数主要有下面四个：  
1，`REGEXP_LIKE` ：与LIKE的功能相似  
2，`REGEXP_INSTR` ：与INSTR的功能相似  
3，`REGEXP_SUBSTR` ：与SUBSTR的功能相似  
4，`REGEXP_REPLACE` ：与REPLACE的功能相似  
它们在用法上与Oracle SQL 函数`LIKE、INSTR、SUBSTR` 和`REPLACE` 用法相同，  
但是它们使用`POSIX` 正则表达式代替了老的百分号（`%`）和通配符（`_`）字符。  
`POSIX` 正则表达式由标准的元字符（`metacharacters`）所构成：  
`'^'` 匹配输入字符串的开始位置，在方括号表达式中使用，此时它表示不接受该字符集合。  
`'$'` 匹配输入字符串的结尾位置。如果设置了 `RegExp` 对象的 `Multiline` 属性，则 `$` 也匹配 `'\n'` 或 `'\r'`。  
`'.'` 匹配除换行符之外的任何单字符。  
`'?'` 匹配前面的子表达式零次或一次。  
`'+'` 匹配前面的子表达式一次或多次。  
`'*'` 匹配前面的子表达式零次或多次。  
`'|'` 指明两项之间的一个选择。例子`'^([a-z]+|[0-9]+)$'`表示所有小写字母或数字组合成的  
字符串。  
`'( )'` 标记一个子表达式的开始和结束位置。  
`'[]'` 标记一个中括号表达式。  
`'{m,n}'` 一个精确地出现次数范围，m=<出现次数<=n，’\{m\}’表示出现m次，’\{m,\}’表示至少出现m次。  
`\num` 匹配 `num`，其中 `num` 是一个正整数。对所获取的匹配的引用。  
**字符簇：**  
`[[:alpha:]]` 任何字母。  
`[[:digit:]]` 任何数字。  
`[[:alnum:]]` 任何字母和数字。  
`[[:space:]]` 任何白字符。  
`[[:upper:]]` 任何大写字母。  
`[[:lower:]]` 任何小写字母。  
`[[:punct:]]` 任何标点符号。  
`[[:xdigit:]]` 任何16进制的数字，相当于`[0-9a-fA-F]`。  
**各种操作符的运算优先级**  
`/`转义符  
`(), (?:), (?=), []` 圆括号和方括号  
`*, +, ?, {n}, {n,}, {n,m}` 限定符  
`^, $, anymetacharacter` 位置和顺序  
`|`  
`*/`

--创建表
    create table fzq ( id varchar(4), value varchar(10) );
    --数据插入
    insert into fzq values ('1','1234560');
    insert into fzq values ('2','1234560');
    insert into fzq values ('3','1b3b560');
    insert into fzq values ('4','abc');
    insert into fzq values ('5','abcde');
    insert into fzq values ('6','ADREasx');
    insert into fzq values ('7','123 45');
    insert into fzq values ('8','adc de');
    insert into fzq values ('9','adc,.de');
    insert into fzq values ('10','1B');
    insert into fzq values ('10','abcbvbnb');
    insert into fzq values ('11','11114560');
    insert into fzq values ('11','11124560');
    --regexp_like
    --查询value中以1开头60结束的记录并且长度是7位
    select * from fzq where value like '1____60';
    select * from fzq where regexp_like(value,'1....60');
    --查询value中以1开头60结束的记录并且长度是7位并且全部是数字的记录。
    --使用like就不是很好实现了。
    select * from fzq where regexp_like(value,'1[0-9]{4}60');
    -- 也可以这样实现，使用字符集。
    select * from fzq where regexp_like(value,'1[[:digit:]]{4}60');
    -- 查询value中不是纯数字的记录
    select * from fzq where not regexp_like(value,'^[[:digit:]]+$');
    -- 查询value中不包含任何数字的记录。
    select * from fzq where regexp_like(value,'^[^[:digit:]]+$');
    --查询以12或者1b开头的记录.不区分大小写。
    select * from fzq where regexp_like(value,'^1[2b]','i');
    --查询以12或者1b开头的记录.区分大小写。
    select * from fzq where regexp_like(value,'^1[2B]');
    -- 查询数据中包含空白的记录。
    select * from fzq where regexp_like(value,'[[:space:]]');
    --查询所有包含小写字母或者数字的记录。
    select * from fzq where regexp_like(value,'^([a-z]+|[0-9]+)$');
    --查询任何包含标点符号的记录。
    select * from fzq where regexp_like(value,'[[:punct:]]');

例子：判断姓名是否为空，少于两个字符，包含数字和字母

create or replace
    FUNCTION CheckName(NameStr in VARCHAR2) RETURN integer
    As
    BEGIN
    --符合返回1，不符合返回0
       if(NameStr is null or length(NameStr)<2) then
          return 0;
       else
          if(NameStr like '%未取名%') then
           RETURN 0;
           end if;
           if regexp_like(NameStr,'^([a-z]+|[0-9]+|[A-Z]+)$') then
           return 0;
           end if; 
           return 1;      
       end if;
    END CheckName;

--------------------

例子：

create or replace procedure p_jh_cbs_oc_datagenerate(ddate in varchar2,result_code out varchar2, result_info out varchar2) is v_ddate varchar2(8);
          v_sql   varchar2(4000);
        begin if ddate is null then v_ddate := to_char(sysdate - 1, 'YYYYMMDD');
          elsif length(ddate) = 8 then
            v_ddate := ddate;
          else
            result_code := '9999';
            result_info := '日期输入不合法,进程终止';
            return;
          end if;
          begin execute immediate 'truncate table t_v_sql';
            v_sql := 'insert into t_dc_order_center_detail (ACCEPT_DATE, CITY_CODE, CHANNEL_CODE, TRADE_ID, PAY_MODE, PAPAY_DEPAR, PAY_STATE, PAY_DATE, PAY_ORDERID, PAY_FEE, ORDER_FEE, ORDER_SOURCE, ADVANCE_PAY, SHIP_MODE, SHIPPING_ID, TRADE_STATE, TRADE_TYPE_CODE, PRODUCT_ID, PRODUCT_NAME, SERIAL_NUMBER, ACCOUT_ID, ACCOUT_NAME, TRADE_STAFF_ID, TRADE_STAFFNAME, DEVELOP_NAME, DEVELOP_ID, REMARK, USER_TYPE, CUST_NAME, PSPT_ID, INVALID_DATE, INPUT_DATE, ORDER_TYPE, RELATION_ORDERS, PROPT_CODE, PROP_VALUE, IMPORT_DATE, RESV1, RESV2, RESV3, RESV4, RESV5) select ACCEPT_DATE, CITY_CODE, CHANNEL_CODE, TRADE_ID, PAY_MODE, PAPAY_DEPAR, PAY_STATE, PAY_DATE, PAY_ORDERID, PAY_FEE, ORDER_FEE, ORDER_SOURCE, ADVANCE_PAY, SHIP_MODE, SHIPPING_ID, TRADE_STATE, TRADE_TYPE_CODE, PRODUCT_ID, PRODUCT_NAME, SERIAL_NUMBER, ACCOUT_ID, ACCOUT_NAME, TRADE_STAFF_ID, TRADE_STAFFNAME, DEVELOP_NAME, DEVELOP_ID, REMARK, USER_TYPE, CUST_NAME, PSPT_ID, INVALID_DATE, INPUT_DATE, ORDER_TYPE, RELATION_ORDERS, PROPT_CODE, PROP_VALUE, IMPORT_DATE, RESV1, RESV2, DECODE((SELECT DEVELOP_ID FROM DUAL WHERE REGEXP_LIKE(DEVELOP_ID,''''(WX|KF|DX|ZW) '''')),'',''''2'''',''''3'''' )) RESV3, RESV4, RESV5 from t_dc_order_center where ACCEPT_DATE=' || '''' || v_ddate || ''''; -- insert into t_v_sql (v_sql) values (v_sql); commit; execute immediate v_sql; commit; v_sql := 'INSERT INTO T_DC_CB_BS_TRADE_DETAIL ( TRADE_ID, SUBSCRIBE_ID, BPM_ID, TRADE_TYPE_CODE, IN_MODE_CODE, PRIORITY, SUBSCRIBE_STATE, NEXT_DEAL_TAG, PRODUCT_ID, BRAND_CODE, USER_ID, CUST_ID, USECUST_ID, ACCT_ID, USER_DIFF_CODE, NET_TYPE_CODE, SERIAL_NUMBER, CUST_NAME, ACCEPT_DATE, ACCEPT_MONTH, TRADE_STAFF_ID, TRADE_DEPART_ID, TRADE_CITY_CODE, TRADE_EPARCHY_CODE, TERM_IP, EPARCHY_CODE, CITY_CODE, OLCOM_TAG, EXEC_TIME, FINISH_DATE, OPER_FEE, FOREGIFT, ADVANCE_PAY, INVOICE_NO, FEE_STATE, FEE_TIME, FEE_STAFF_ID, CANCEL_TAG, CANCEL_DATE, CANCEL_STAFF_ID, CANCEL_DEPART_ID, CANCEL_CITY_CODE, CANCEL_EPARCHY_CODE, CHECK_TYPE_CODE, CHK_TAG, AUDIT_TAG, AUDIT_BATCH_NO, ACTOR_NAME, ACTOR_CERTTYPEID, ACTOR_PHONE, ACTOR_CERTNUM, CONTACT, CONTACT_PHONE, CONTACT_ADDRESS, REMARK, IF_MAINTENANCE, ORDER_ID, SUB_ORDER_ID, MAIN_DISCNT_CODE, PRODUCT_SPEC, STANDARD_KIND_CODE, IMPORT_DATE, SYS_TYPE ) select TRADE_ID, SUBSCRIBE_ID, BPM_ID, TRADE_TYPE_CODE, IN_MODE_CODE, PRIORITY, SUBSCRIBE_STATE, NEXT_DEAL_TAG, PRODUCT_ID, BRAND_CODE, USER_ID, CUST_ID, USECUST_ID, ACCT_ID, USER_DIFF_CODE, NET_TYPE_CODE, SERIAL_NUMBER, CUST_NAME, ACCEPT_DATE, ACCEPT_MONTH, TRADE_STAFF_ID, TRADE_DEPART_ID, TRADE_CITY_CODE, TRADE_EPARCHY_CODE, TERM_IP, EPARCHY_CODE, CITY_CODE, OLCOM_TAG, EXEC_TIME, FINISH_DATE, OPER_FEE, FOREGIFT, ADVANCE_PAY, INVOICE_NO, FEE_STATE, FEE_TIME, FEE_STAFF_ID, CANCEL_TAG, CANCEL_DATE, CANCEL_STAFF_ID, CANCEL_DEPART_ID, CANCEL_CITY_CODE, CANCEL_EPARCHY_CODE, CHECK_TYPE_CODE, CHK_TAG, AUDIT_TAG, AUDIT_BATCH_NO, ACTOR_NAME, ACTOR_CERTTYPEID, ACTOR_PHONE, ACTOR_CERTNUM, CONTACT, CONTACT_PHONE, CONTACT_ADDRESS, REMARK, IF_MAINTENANCE, ORDER_ID, SUB_ORDER_ID, MAIN_DISCNT_CODE, PRODUCT_SPEC, STANDARD_KIND_CODE, IMPORT_DATE, ''BSS'' FROM T_DC_BSS_TRADE where to_char(ACCEPT_DATE,''YYYYMMDD'')=' || '''' || v_ddate || ''''; --insert into t_v_sql (v_sql) values (v_sql); commit; execute immediate v_sql; v_sql := 'INSERT INTO t_dc_cb_bs_trade_detail (USER_ID, USER_DIFF_CODE, USECUST_ID, TRADE_TYPE_CODE, TRADE_STAFF_ID, TRADE_ID, TRADE_EPARCHY_CODE, TRADE_DEPART_ID, TRADE_CITY_CODE, TERM_IP, SUB_ORDER_ID, SUBSCRIBE_STATE, SUBSCRIBE_ID, STANDARD_KIND_CODE, SERIAL_NUMBER, REMARK, PROVINCE_CODE, PRODUCT_SPEC, PRODUCT_ID, PRIORITY, ORDER_ID, OPER_FEE, OLCOM_TAG, NEXT_DEAL_TAG, NET_TYPE_CODE, MAIN_DISCNT_CODE, IN_MODE_CODE, INVOICE_NO, IMPORT_DATE, IF_MAINTENANCE, FOREGIFT, FINISH_DATE, FEE_TIME, FEE_STATE, FEE_STAFF_ID, EXEC_TIME, CUST_NAME, CUST_ID, CONTACT_PHONE, CONTACT_ADDRESS, CONTACT, CHK_TAG, CHECK_TYPE_CODE, CANCEL_TAG, CANCEL_STAFF_ID, CANCEL_EPARCHY_CODE, CANCEL_DEPART_ID, CANCEL_DATE, CANCEL_CITY_CODE, BRAND_CODE, BPM_ID, AUDIT_TAG, AUDIT_BATCH_NO, ADVANCE_PAY, ACTOR_PHONE, ACTOR_NAME, ACTOR_CERTTYPEID, ACTOR_CERTNUM, ACCT_ID, ACCEPT_MONTH, ACCEPT_DATE, SYS_TYPE ) SELECT USER_ID, USER_DIFF_CODE, USECUST_ID, TRADE_TYPE_CODE, TRADE_STAFF_ID, TRADE_ID, TRADE_EPARCHY_CODE, TRADE_DEPART_ID, TRADE_CITY_CODE, TERM_IP, SUB_ORDER_ID, SUBSCRIBE_STATE, SUBSCRIBE_ID, STANDARD_KIND_CODE, SERIAL_NUMBER, REMARK, PROVINCE_CODE, PRODUCT_SPEC, PRODUCT_ID, PRIORITY, ORDER_ID, OPER_FEE, OLCOM_TAG, NEXT_DEAL_TAG, NET_TYPE_CODE, MAIN_DISCNT_CODE, IN_MODE_CODE, INVOICE_NO, IMPORT_DATE, IF_MAINTENANCE, FOREGIFT, FINISH_DATE, FEE_TIME, FEE_STATE, FEE_STAFF_ID, to_date(EXEC_TIME,''YYYY-MM-DD HH24:MI:SS''), CUST_NAME, CUST_ID, CONTACT_PHONE, CONTACT_ADDRESS, CONTACT, CHK_TAG, CHECK_TYPE_CODE, CANCEL_TAG, CANCEL_STAFF_ID, CANCEL_EPARCHY_CODE, CANCEL_DEPART_ID, CANCEL_DATE, CANCEL_CITY_CODE, BRAND_CODE, BPM_ID, AUDIT_TAG, AUDIT_BATCH_NO, ADVANCE_PAY, ACTOR_PHONE, ACTOR_NAME, ACTOR_CERTTYPEID, ACTOR_CERTNUM, ACCT_ID, ACCEPT_MONTH, ACCEPT_DATE, ''CBSS'' FROM t_dc_cbss_trade where to_char(ACCEPT_DATE,''YYYYMMDD'')=' || '''' || v_ddate || ''''; -- insert into t_v_sql (v_sql) values (v_sql); commit; execute immediate v_sql; commit; end; commit; result_code := '0000'; result_info := '存储过程执行完成'; exception when others then result_code := '9999'; result_info := '存储过程执行出错，错误如下:' || sqlerrm; dbms_output.put_line('存储过程执行出错，错误如下:' || sqlerrm); rollback; end;