CODEWITHSUNDEEP
regex
Ali
Ali

Reputation: 8100

Regex match from pattern until another pattern

I've a file containing lots of SQLs, including some stored procedures. I want to extract the stored procedures only. The SQL may look like the following

    SHOW PROCEDURE SECADMIN.NewTUTRMU_User_new              ;

    3 Rows returned. DBS time = 00:00:00

    RequestText
    REPLACE PROCEDURE SecAdmin.NewTUTRMU_User_new
                    (
                    IN UserName       VARCHAR(128),
                    IN SARNumber      VARCHAR(10),                

                    )
    /*
   come comments                                                    

    */

    BEGIN

    DECLARE emOwnerDeptI VARCHAR(20);

    SEL SUBSTRING(:UserName FROM 8) INTO :GDWUserid;

    CALL DBC.SysExecSql('
    CREATE USER '||:UserName||' ...
...
...
END;

...
Then some more SQL and then another stored procedure definition
...

SHOW PROCEDURE SECADMIN.ReInstateUser_new               ;

3 Rows returned. DBS time = 00:00:00

RequestText
REPLACE PROCEDURE SECADMIN.ReInstateUser_new
                  ( 

                   IN UserName VARCHAR(128)
                   --Must be full employee ID including leading 0's
                  ,IN Empl_I VARCHAR(12),
                                     IN Task_Num ) 

/*
   MODIFICATION HISTORY:
   */

BEGIN               

  DECLARE newPassword VARCHAR(20);
...
...
END;
...
...

I came up with the following RegEx which does not work and matches the text until it find a semi-colon.

REPLACE PROCEDURE.*[^;]*;

I need a regex that matches the text from REPLACE PROCEDURE to END;

Please suggest.

Upvotes: 0

Views: 80

Answers (1)

Javier Buzzi
Javier Buzzi

Reputation: 6808

Based on the example provided, i think this will do it: /REPLACE PROCEDURE [\w\W]+?END;/g

Upvotes: 1

Related Questions