Why does regexp_replace replaces nothing with something?

Question

I have to update several scripts to use a table from another schema: S1.MYTABLE => S2.MYTABLE2.

Also when I replace the name of the table I want to keep the format of the original script. Keeping enter or space after S2.MYTABLE2 if there was one after S1.MYTABLE

My code seems to work fine, but at the end of the string concats S2.MYTABLE2.

This is my tests script: (this is just simplified example of my problem)

DECLARE
   testtext VARCHAR2(2000) := 'CREATE OR REPLACE FUNCTION "S1"."MY_FUNCTION" (PARAMETER1 VARCHAR2)
   RETURN VARCHAR2
AS
   something1    VARCHAR2 (50);
   OUTsomething   VARCHAR2 (50);
BEGIN
   BEGIN
      SELECT COLUMN1
        INTO OUTsomething
        FROM S1.MYTABLE_RELATED
       WHERE COLUMN2 = ''xx'' AND COLUMN2 = PARAMETER1;

       SELECT COLUMN1
        INTO OUTsomething
        FROM S1.MYTABLE
       WHERE COLUMN2 = ''xx'' AND COLUMN2 = PARAMETER1;

       SELECT COLUMN1
        INTO OUTsomething
        FROM S1.MYTABLE WHERE COLUMN2 = ''xx'' AND COLUMN2 = PARAMETER1;                                           
   RETURN OUTsomething;

   SELECT COLUMN1
        INTO OUTsomething
        FROM S1.MYTABLE                                  
        WHERE COLUMN2 = ''xx'' AND COLUMN2 = PARAMETER1;                                           
   RETURN OUTsomething;
END;';

   script CLOB;
   owner VARCHAR2(30) := 'S1';
   object_name VARCHAR2(30) := 'MYTABLE';
   output_name VARCHAR2(30) := 'MYTABLE2';
   destination VARCHAR2(30) := 'S2';

BEGIN

        script := regexp_replace(testtext, OWNER || '[[:space:]]*\.[[:space:]]*' || OBJECT_NAME || ' ', DESTINATION || '.' || OUTPUT_NAME || ' ',1, 0, 'i');
        script := regexp_replace(script, OWNER || '[[:space:]]*\.[[:space:]]*' || OBJECT_NAME || '\s|($)', DESTINATION || '.' || OUTPUT_NAME || CHR(13) || CHR(10),1, 0, 'i');                
        DBMS_OUTPUT.PUT_LINE(script);                    
END;

The code of the function MY_FUNCTION, although unrealistic, exemplifies all the cases I am dealing with.

Result of running this script:

CREATE OR REPLACE FUNCTION "S1"."MY_FUNCTION" (PARAMETER1 VARCHAR2)
   RETURN VARCHAR2
AS
   something1    VARCHAR2 (50);
   OUTsomething   VARCHAR2 (50);
BEGIN
   BEGIN
      SELECT COLUMN1
        INTO OUTsomething
        FROM S1.MYTABLE_RELATED
       WHERE COLUMN2 = 'xx' AND COLUMN2 = PARAMETER1;

       SELECT COLUMN1
        INTO OUTsomething
        FROM S2.MYTABLE2
       WHERE COLUMN2 = 'xx' AND COLUMN2 = PARAMETER1;

       SELECT COLUMN1
        INTO OUTsomething
        FROM S2.MYTABLE2 WHERE COLUMN2 = 'xx' AND COLUMN2 = PARAMETER1;                                           
   RETURN OUTsomething;

   SELECT COLUMN1
        INTO OUTsomething
        FROM S2.MYTABLE2                                  
        WHERE COLUMN2 = 'xx' AND COLUMN2 = PARAMETER1;                                           
   RETURN OUTsomething;
END;S2.MYTABLE2

As you can see S1.MYTABLE has been replaced by S2.MYTABLE2 in all relevant places, maintaining the originally formatting (spaces and enters).

But for some reason S2.MYTABLE2 is concatenated at the end.

Why is regexp_replace concattenating S2.MYTABLE2 at the end of the script?

Is there a better way to achieve what I want? Replace a table with another, keeping the format.

David Faber · Accepted Answer · 2015-03-06 20:53:24Z

1

This is the problem:

\s|($)

That represents an alternating match that will end up replacing the end of string anchor with the new text. I think you want this instead:

(\s|$)

answered Mar 6, 2015 at 20:53

David Faber

12.5k2 gold badges33 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

ruudvan · Accepted Answer · 2015-03-06 21:05:13Z

It is probably because of |($) in the second regex. Not sure why you wanted it there.

On removing that -

DECLARE
   testtext      VARCHAR2 (2000)
      := 'CREATE OR REPLACE FUNCTION "S1"."MY_FUNCTION" (PARAMETER1 VARCHAR2)
   RETURN VARCHAR2
AS
   something1    VARCHAR2 (50);
   OUTsomething   VARCHAR2 (50);
BEGIN
   BEGIN
      SELECT COLUMN1
        INTO OUTsomething
        FROM S1.MYTABLE_RELATED
       WHERE COLUMN2 = ''xx'' AND COLUMN2 = PARAMETER1;

       SELECT COLUMN1
        INTO OUTsomething
        FROM S1.MYTABLE
       WHERE COLUMN2 = ''xx'' AND COLUMN2 = PARAMETER1;

       SELECT COLUMN1
        INTO OUTsomething
        FROM S1.MYTABLE WHERE COLUMN2 = ''xx'' AND COLUMN2 = PARAMETER1;                                           
   RETURN OUTsomething;

   SELECT COLUMN1
        INTO OUTsomething
        FROM S1.MYTABLE                                  
        WHERE COLUMN2 = ''xx'' AND COLUMN2 = PARAMETER1;                                           
   RETURN OUTsomething;
END;';

   script        CLOB;
   owner         VARCHAR2 (30) := 'S1';
   object_name   VARCHAR2 (30) := 'MYTABLE';
   output_name   VARCHAR2 (30) := 'MYTABLE2';
   destination   VARCHAR2 (30) := 'S2';
BEGIN
   script      :=
      REGEXP_REPLACE (
         testtext,
         OWNER || '[[:space:]]*\.[[:space:]]*' || OBJECT_NAME || ' ',
         DESTINATION || '.' || OUTPUT_NAME || ' ',
         1,
         0,
         'i');
   script      :=
      REGEXP_REPLACE (
         script,
         OWNER || '[[:space:]]*\.[[:space:]]*' || OBJECT_NAME || '\s',
         DESTINATION || '.' || OUTPUT_NAME || CHR (13) || CHR (10),
         1,
         0,
         'i');
   DBMS_OUTPUT.PUT_LINE (script);
END;

Here is the output without the table name at the end -

CREATE OR REPLACE FUNCTION "S1"."MY_FUNCTION" (PARAMETER1 VARCHAR2)
   RETURN VARCHAR2
AS
   something1    VARCHAR2 (50);
   OUTsomething   VARCHAR2 (50);
BEGIN
   BEGIN
      SELECT COLUMN1
        INTO OUTsomething
        FROM S1.MYTABLE_RELATED
       WHERE COLUMN2 = 'xx' AND COLUMN2 = PARAMETER1;

       SELECT COLUMN1
        INTO OUTsomething
        FROM S2.MYTABLE2
       WHERE COLUMN2 = 'xx' AND COLUMN2 = PARAMETER1;

       SELECT COLUMN1
        INTO OUTsomething
        FROM S2.MYTABLE2 WHERE COLUMN2 = 'xx' AND COLUMN2 = PARAMETER1;                                           
   RETURN OUTsomething;

   SELECT COLUMN1
        INTO OUTsomething
        FROM S2.MYTABLE2                                  
        WHERE COLUMN2 = 'xx' AND COLUMN2 = PARAMETER1;                                           
   RETURN OUTsomething;
END;

EDIT: You may want to do what @DavidFaber's answer says in case your regex pattern may also appear at the end of the entire string (not just the end of a line).

Collectives™ on Stack Overflow

Why does regexp_replace replaces nothing with something?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related