更新时间:2022-06-13 GMT+08:00

正则表达式函数

正则表达式使用标准化的语法约定来指定匹配字符串的模式。在Oracle中,正则表达式通过一组允许用户搜索和操作字符串数据的SQL函数来实现。

DSC可迁移REGEXP_INSTRREGEXP_SUBSTRREGEXP_REPLACE正则表达式,详情如下:

  • 不支持包含sub_expr参数的Regexp(REGEXP_INSTR和REGEXP_SUBSTR)。若输入脚本包含sub_expr,则DSC将为其记录为错误。
  • Regexp(REGEXP_INSTR、REGEXP_SUBSTR和REGXP_REPLACE)使用match_param参数来设置默认的匹配行为。DSC中,该参数仅支持“i”值(匹配不区分大小写)和“c”值(匹配区分大小写),不支持其他值。
  • Regexp(REGEXP_INSTR)使用return_option参数为regexp设置匹配的返回值。DSC仅支持此参数设为0,不支持其他值。

REGEXP_INSTR

REGEXP_INSTR扩展了INSTR函数的功能,支持搜索字符串的正则表达式模式。DSC可迁移含有2到6个参数的REGEXP_INSTR。

sub_expr参数(参数#7)在Oracle中可用,但不支持迁移。如果输入脚本包含sub_expr,DSC会将其记录为错误。

支持将return_option设为0,不支持其他值。

支持将match_param设为“i”(匹配不区分大小写)和“c”(匹配区分大小写),不支持其他值。
REGEXP_INSTR(  
     string, 
      pattern, 
      [start_position,] 
      [nth_appearance,] 
      [return_option,] 
      [match_param,] 
      [sub_expr] 
)

Bulk操作

  • 输入:REGEXP_INSTR
SELECT
          REGEXP_INSTR( 'TechOnTheNet is a great resource' ,'t' )
     FROM
          dual
;

输出

SELECT
          MIG_ORA_EXT.REGEXP_INSTR (
               'TechOnTheNet is a great resource'
               ,'t'
          )
     FROM
          dual
;
  • 输入:REGEXP_INSTR,使用7个参数(无效)
SELECT
          Empno
          ,ename
          ,REGEXP_INSTR( ename ,'a|e|i|o|u' ,1 ,1 ,0 ,'i' ,7 ) AS Dname
     FROM
          emp19
;

输出

输入表达式含有7个参数,但MT仅允许REGEXP_INSTR包含2到6个参数,因此会记录错误 “Seven(7) arguments for REGEXP_INSTR function is not supported."。

SELECT
          Empno
          ,ename
          ,REGEXP_INSTR( ename ,'a|e|i|o|u' ,1 ,1 ,0 ,'i' ,7 ) AS Dname
     FROM
          emp19
;

BLogic操作

  • 输入:REGEXP_INSTR
CREATE OR REPLACE FUNCTION myfct
RETURN VARCHAR2
IS
res VARCHAR2(200) ;
BEGIN
    res := 100 ;
    INSERT INTO emp19 RW(RW.empno,RW.ename,dname) SELECT res, RWN.ename key
, regexp_instr(ename ,'[ae]',4,2,0, 'i')   as Dname FROM   emp19 RWN ;

    RETURN res ;
END ;
/

输出

CREATE
     OR REPLACE FUNCTION myfct RETURN VARCHAR2 IS res VARCHAR2 ( 200 ) ;
     BEGIN
          res := 100 ;
          INSERT INTO emp19 ( empno ,ename ,dname ) SELECT
               res ,RWN.ename "KEY" ,MIG_ORA_EXT.REGEXP_INSTR ( ename ,'[ae]' ,4 ,2 ,0 ,'i' ) as Dname
          FROM
               emp19 RWN ;
               RETURN res ; END ;
/

REGEXP_SUBSTR

REGEXP_SUBSTR通过支持搜索字符串的正则表达式模式来扩展SUBSTR函数的功能。可迁移含有2到5个参数的REGEXP_SUBSTR。

sub_expr参数(参数#6)在Oracle中可用,但不支持迁移。如果输入脚本包含sub_expr,则DSC会将其记录为错误。

支持将match_param设为“i”(匹配不区分大小写)和“c”(匹配区分大小写),不支持其他值。

REGEXP_SUBSTR(  
    string,  
      pattern, 
      [start_position,] 
      [nth_appearance,] 
      [match_param,] 
      [sub_expr] 
 )

Bulk操作

  • 输入:REGEXP_SUBSTR
SELECT
          Ename
          ,REGEXP_SUBSTR( 'Programming' ,'(\w).*?\1' ,1 ,1 ,'i' )
     FROM
          emp16
;

输出

SELECT
          Ename
          ,MIG_ORA_EXT.REGEXP_SUBSTR (
               'Programming'
               ,'(\w).*?\1'
               ,1
               ,1
               ,'i'
          )
     FROM
          emp16
;
  • 输入:REGEXP_SUBSTR
SELECT
          REGEXP_SUBSTR( '1234567890' ,'(123)(4(56)(78))' ,1 ,1 ,'i'  ) "REGEXP_SUBSTR"
     FROM
          DUAL
;

输出

SELECT
          MIG_ORA_EXT.REGEXP_SUBSTR (
               '1234567890'
               ,'(123)(4(56)(78))'
               ,1
               ,1
               ,'i'
          ) "REGEXP_SUBSTR"
     FROM
          DUAL
;
  • 输入REGEXP_SUBSTR,使用6个参数(无效)
SELECT
          REGEXP_SUBSTR( '1234567890' ,'(123)(4(56)(78))' ,1 ,1 ,'i' ,1 ) "REGEXP_SUBSTR"
     FROM
          DUAL
;

输出

输入表达式含有6个参数,但MT仅支持REGEXP_SUBSTR含有2到5个参数,所以会记录错误" Error message :Six(6) arguments for REGEXP_SUBSTR function is not supported."。

SELECT
          REGEXP_SUBSTR( '1234567890' ,'(123)(4(56)(78))' ,1 ,1 ,'i' ,1 ) "REGEXP_SUBSTR"
     FROM
          DUAL
;

BLogic操作

  • 输入:REGEXP_SUBSTR
CREATE OR REPLACE FUNCTION myfct
RETURN VARCHAR2
IS
res VARCHAR2(200) ;
BEGIN
    res := 100 ;
    INSERT INTO emp19 RW(RW.empno,RW.ename,dname) SELECT res, RWN.ename key
,REGEXP_ SUBSTR ('TechOnTheNet', 'a|e|i|o|u', 1, 1, 'i') as Dname FROM   emp19 RWN ;

    RETURN res ;
END ;
/

输出

CREATE
     OR REPLACE FUNCTION myfct RETURN VARCHAR2 IS res VARCHAR2 ( 200 ) ;
     BEGIN
          res := 100 ;
          INSERT INTO emp19 ( empno ,ename ,dname ) SELECT
               res ,RWN.ename "KEY" ,MIG_ORA_EXT.REGEXP_ SUBSTR ( 'TechOnTheNet' ,'a|e|i|o|u' ,1 ,1 ,'i' ) as Dname
          FROM
               emp19 RWN ;
               RETURN res ;
END ;
/

REGEXP_REPLACE

REGEXP_REPLACE通过支持搜索字符串的正则表达式模式来扩展REPLACE函数的功能。可迁移含有2到6个参数的REGEXP_REPLACE。

支持将match_param设为“i”(匹配不区分大小写)和“c”(匹配区分大小写),不支持其他值。

REGEXP_REPLACE( 
      string, 
      pattern,
      [replacement_string,]
      [start_position,]
      [nth_appearance,]
      [match_param]
 )

Bulk操作

  • 输入:REGEXP_REPLACE
SELECT
          testcol
          ,regexp_replace( testcol ,'([[:digit:]]{3})\.([[:digit:]]{3})\.([[:digit:]]{4})' ,'(\1) \2-\3' ) RESULT
     FROM
          test
     WHERE
          LENGTH( testcol ) = 12
;

输出

SELECT
          testcol
          ,MIG_ORA_EXT.REGEXP_REPLACE (
               testcol
               ,'([[:digit:]]{3})\.([[:digit:]]{3})\. ([[:digit:]]{4})'
               ,'(\1) \2-\3'
          ) RESULT
     FROM
          test
     WHERE
          LENGTH( testcol ) = 12
;
  • 输入:REGEXP_REPLACE
SELECT
          UPPER( regexp_replace ( 'foobarbequebazilbarfbonk barbeque' ,'(b[^b]+)(b[^b]+)' ) )
     FROM
          DUAL
;

输出

SELECT
          UPPER( MIG_ORA_EXT.REGEXP_REPLACE ( 'foobarbequebazilbarfbonk barbeque' ,'(b[^b]+)(b[^b]+)' ) )
     FROM
          DUAL
;
  • 输入:REGEXP_REPLACE,使用7个参数(无效)
SELECT
          REGEXP_REPLACE( 'TechOnTheNet' ,'a|e|i|o|u' ,'Z' ,1 ,1 ,'i' ,'(\1) \2-\3' ) AS First_Occurrence
     FROM
          emp
;

输出

输入表达式含有7个参数,但MT仅支持REGEXP_REPLACE含有2至6个参数,因此会记录错误“Too many arguments for REGEXP_REPLACE function [Max:6 argument(s) is/are allowed].

SELECT
          REGEXP_REPLACE( 'TechOnTheNet' ,'a|e|i|o|u' ,'Z' ,1 ,1 ,'i' ,'(\1) \2-\3' ) AS First_Occurrence
     FROM
          emp
;

BLogic操作

  • 输入:REGEXP_REPLACE
CREATE OR REPLACE FUNCTION myfct
RETURN VARCHAR2
IS
res VARCHAR2(200) ;
BEGIN
    res := 100 ;
    INSERT INTO emp19 RW(RW.empno,RW.ename,dname) SELECT res, RWN.ename key
,REGEXP_REPLACE ('TechOnTheNet', 'a|e|i|o|u', 'Z', 1, 1, 'i') as Dname FROM   emp19 RWN ;

    RETURN res ;
END ;
/

输出

CREATE
     OR REPLACE FUNCTION myfct RETURN VARCHAR2 IS res VARCHAR2 ( 200 ) ;
     BEGIN
          res := 100 ;
          INSERT INTO emp19 ( empno ,ename ,dname ) SELECT
               res ,RWN.ename "KEY" ,MIG_ORA_EXT.REGEXP_REPLACE ( 'TechOnTheNet' ,'a|e|i|o|u' ,'Z' ,1 ,1 ,'i' ) as Dname
          FROM
               emp19 RWN ;
               RETURN res ;
END ;
/

LISTAGG/regexp_replace/regexp_instr

设置以下参数后,可以迁移LISTAGG/regexp_replace/regexp_instr:

  • MigSupportForListAgg=false
  • MigSupportForRegexReplace=false

输入: LISTAGG/regexp_replace/regexp_instr

SELECT LISTAGG(T.OS_SOFTASSETS_ID,',') WITHIN GROUP(ORDER BY T.SOFTASSETS_ID)
       INTO V_OS_SOFTASSETS_IDS
       FROM SPMS_SYSSOFT_PROP_APPR T
      WHERE T.APPR_ID = I_APPR_ID
        AND T.SYSSOFT_PROP = '001';
 
V_ONLY_FILE_NAME := REGEXP_REPLACE( I_FILENAME ,'.*/' ,'' ) ;
 
THEN v_auth_type := 102;
         ELSIF v_status IN ('0100', '0200')
               AND REGEXP_INSTR (v_role_str, ',(411|414),') > 0

输出

"SELECT LISTAGG(T.OS_SOFTASSETS_ID,',') WITHIN GROUP(ORDER BY T.SOFTASSETS_ID)
       INTO V_OS_SOFTASSETS_IDS
       FROM SPMS_SYSSOFT_PROP_APPR T
      WHERE T.APPR_ID = I_APPR_ID
        AND T.SYSSOFT_PROP = '001';
 
V_ONLY_FILE_NAME := REGEXP_REPLACE (I_FILENAME, '.*/', '');
 
THEN  v_auth_type := 102;
         ELSIF v_status IN ('0100', '0200')
                   AND REGEXP_INSTR (v_role_str, ',(411|414),') > 0"