On this page

Show all

Help Center/ GaussDB(DWS)/ Tool Guide/ DSC: SQL Syntax Migration Tool/ Oracle Syntax Migration/ System Functions/ Regular Expression Functions

Regular Expression Functions

Updated on 2023-04-03 GMT+08:00

View PDF

Regular expressions specify patterns to match strings using standardized syntax conventions. In Oracle, regular expressions are implemented using a set of SQL functions that allow you to search and use string data.

DSC can migrate REGEXP_INSTR, REGEXP_SUBSTR, and REGEXP_REPLACE regular expressions. Details are as follows:

Regexp (REGEXP_INSTR and REGEXP_SUBSTR) that includes the sub_expr parameter are not supported. If the input script includes sub_expr, the DSC will log an error for it.
Regexp (REGEXP_INSTR, REGEXP_SUBSTR, and REGXP_REPLACE) uses the match_param parameter to set the default matching behavior. The DSC supports values i (case-insensitive) and c (case-sensitive) for this parameter. Other values for match_param are not supported.
Regexp (REGEXP_INSTR) uses the return_option parameter to set what is returned for regexp. The DSC supports the value 0 (zero) for this parameter. Other values for return_option are not supported.

REGEXP_INSTR

REGEXP_INSTR extends the functionality of the INSTR function by supporting the regular expression pattern for the search string. REGEXP_INSTR with 2 to 6 parameters are supported for migration.

The sub_expr parameter (parameter #7) is available in Oracle but is not supported for migration. If the input script includes sub_expr, the DSC will log an error for it.

For return_option, the value 0 (zero) is supported. Other values for return_option are not supported.

For match_param, values i (case-insensitive) and c (case-sensitive) are supported. Other values for match_param are not supported.

REGEXP_INSTR(  
     string, 
      pattern, 
      [start_position,] 
      [nth_appearance,] 
      [return_option,] 
      [match_param,] 
      [sub_expr] 
)

Bulk Operations

Input - REGEXP_INSTR

SELECT
          REGEXP_INSTR( 'TechOnTheNet is a great resource' ,'t' )
     FROM
          dual
;

Output

SELECT
          MIG_ORA_EXT.REGEXP_INSTR (
               'TechOnTheNet is a great resource'
               ,'t'
          )
     FROM
          dual
;

Input - REGEXP_INSTR with 7 parameters (Invalid)

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

Output

The input expression has 7 parameters. Since the tool supports REGEXP_INSTR with 2 to 6 arguments, an error will be logged, starting "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 Operations

Input - 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 ;
/

Output

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 extends the functionality of the SUBSTR function by supporting regular expression pattern for the search string. REGEXP_SUBSTR with 2 to 5 parameters are supported for migration.

The sub_expr parameter (parameter #6) is available in Oracle but is not supported for migration. If the input script includes sub_expr, the DSC will log an error for it.

For match_param, values i (case-insensitive) and c (case-sensitive) are supported. Other values for match_param are not supported.

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

Bulk Operations

Input - REGEXP_SUBSTR

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

Output

SELECT
          Ename
          ,MIG_ORA_EXT.REGEXP_SUBSTR (
               'Programming'
               ,'(\w).*?\1'
               ,1
               ,1
               ,'i'
          )
     FROM
          emp16
;

Input - REGEXP_SUBSTR

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

Output

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

Input - REGEXP_SUBSTR with 6 parameters (Invalid)

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

Output

The input expression has 6 arguments. Since the tool supports REGEXP_SUBSTR with 2 to 5 parameters an error will be logged, starting "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 Operations

Input - 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 ;
/

Output

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 extends the functionality of the REPLACE function by supporting regular expression pattern for the search string. REGEXP_REPLACE with 2 to 6 parameters are supported for migration.

For match_param, values i (case-insensitive) and c (case-sensitive) are supported. Other values for match_param are not supported.

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

Bulk Operations

Input - REGEXP_REPLACE

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

Output

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

Input - REGEXP_REPLACE

SELECT
          UPPER( regexp_replace ( 'foobarbequebazilbarfbonk barbeque' ,'(b[^b]+)(b[^b]+)' ) )
     FROM
          DUAL
;

Output

SELECT
          UPPER( MIG_ORA_EXT.REGEXP_REPLACE ( 'foobarbequebazilbarfbonk barbeque' ,'(b[^b]+)(b[^b]+)' ) )
     FROM
          DUAL
;

Input - REGEXP_REPLACE with 7 parameters (Invalid)

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

Output

The input expression has 7 parameters. Since the tool supports REGEXP_REPLACE with 2 to 6 parameters, an error will be logged, starting "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 Operations

Input - 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 ;
/

Output

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

Configure the following parameters before migrating LISTAGG/regexp_replace/regexp_instr:

MigSupportForListAgg=false
MigSupportForRegexReplace=false

Input- REMOVE 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

Output

"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"

Parent topic: System Functions

Previous topic: Analytical Functions

Next topic: PL/SQL