Compute
Elastic Cloud Server
Huawei Cloud Flexus
Bare Metal Server
Auto Scaling
Image Management Service
Dedicated Host
FunctionGraph
Cloud Phone Host
Huawei Cloud EulerOS
Networking
Virtual Private Cloud
Elastic IP
Elastic Load Balance
NAT Gateway
Direct Connect
Virtual Private Network
VPC Endpoint
Cloud Connect
Enterprise Router
Enterprise Switch
Global Accelerator
Management & Governance
Cloud Eye
Identity and Access Management
Cloud Trace Service
Resource Formation Service
Tag Management Service
Log Tank Service
Config
OneAccess
Resource Access Manager
Simple Message Notification
Application Performance Management
Application Operations Management
Organizations
Optimization Advisor
IAM Identity Center
Cloud Operations Center
Resource Governance Center
Migration
Server Migration Service
Object Storage Migration Service
Cloud Data Migration
Migration Center
Cloud Ecosystem
KooGallery
Partner Center
User Support
My Account
Billing Center
Cost Center
Resource Center
Enterprise Management
Service Tickets
HUAWEI CLOUD (International) FAQs
ICP Filing
Support Plans
My Credentials
Customer Operation Capabilities
Partner Support Plans
Professional Services
Analytics
MapReduce Service
Data Lake Insight
CloudTable Service
Cloud Search Service
Data Lake Visualization
Data Ingestion Service
GaussDB(DWS)
DataArts Studio
Data Lake Factory
DataArts Lake Formation
IoT
IoT Device Access
Others
Product Pricing Details
System Permissions
Console Quick Start
Common FAQs
Instructions for Associating with a HUAWEI CLOUD Partner
Message Center
Security & Compliance
Security Technologies and Applications
Web Application Firewall
Host Security Service
Cloud Firewall
SecMaster
Anti-DDoS Service
Data Encryption Workshop
Database Security Service
Cloud Bastion Host
Data Security Center
Cloud Certificate Manager
Edge Security
Managed Threat Detection
Blockchain
Blockchain Service
Web3 Node Engine Service
Media Services
Media Processing Center
Video On Demand
Live
SparkRTC
MetaStudio
Storage
Object Storage Service
Elastic Volume Service
Cloud Backup and Recovery
Storage Disaster Recovery Service
Scalable File Service Turbo
Scalable File Service
Volume Backup Service
Cloud Server Backup Service
Data Express Service
Dedicated Distributed Storage Service
Containers
Cloud Container Engine
SoftWare Repository for Container
Application Service Mesh
Ubiquitous Cloud Native Service
Cloud Container Instance
Databases
Relational Database Service
Document Database Service
Data Admin Service
Data Replication Service
GeminiDB
GaussDB
Distributed Database Middleware
Database and Application Migration UGO
TaurusDB
Middleware
Distributed Cache Service
API Gateway
Distributed Message Service for Kafka
Distributed Message Service for RabbitMQ
Distributed Message Service for RocketMQ
Cloud Service Engine
Multi-Site High Availability Service
EventGrid
Dedicated Cloud
Dedicated Computing Cluster
Business Applications
Workspace
ROMA Connect
Message & SMS
Domain Name Service
Edge Data Center Management
Meeting
AI
Face Recognition Service
Graph Engine Service
Content Moderation
Image Recognition
Optical Character Recognition
ModelArts
ImageSearch
Conversational Bot Service
Speech Interaction Service
Huawei HiLens
Video Intelligent Analysis Service
Developer Tools
SDK Developer Guide
API Request Signing Guide
Terraform
Koo Command Line Interface
Content Delivery & Edge Computing
Content Delivery Network
Intelligent EdgeFabric
CloudPond
Intelligent EdgeCloud
Solutions
SAP Cloud
High Performance Computing
Developer Services
ServiceStage
CodeArts
CodeArts PerfTest
CodeArts Req
CodeArts Pipeline
CodeArts Build
CodeArts Deploy
CodeArts Artifact
CodeArts TestPlan
CodeArts Check
CodeArts Repo
Cloud Application Engine
MacroVerse aPaaS
KooMessage
KooPhone
KooDrive

Regular Expression Functions

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

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"

We use cookies to improve our site and your experience. By continuing to browse our site you accept our cookie policy. Find out more

Feedback

Feedback

Feedback

0/500

Selected Content

Submit selected content with the feedback