Regular Expression For Procedure Definition

Question

Hi,

I need regular expression for folowing line.

CREATE OR REPLACE PROCEDURE [procedure_name]

1. CREATE is a mandatory string and 'OR REPLACE' should occur 0 or 1 time.

Please help me out in creating this expression.

Thanks in advance.

Khanna

Answer 1

Dim pattern = "CREATE\s+(?<or>OR\s+REPLACE)?(?(or)\s+)PROCEDURE\s+\w+"

This pattern has additional check: if there's "OR REPLACE" text in string, it also checks that there's also at least one space between "REPLACE" and "PROCEDURE". Without this check you will get double space check which will give you wrong result when you omit "OR REPLACE" (and when you have one space between "CREATE" and "Procedure").

Good luck!

---

There is no knowledge that is not power.

Answer 2

Thanks fr your help Johny. It worked. Also Can you help me in adding  following to the pattern.

1. To use any other string in place of procedure like Table,Package,Function e.t.c.

2. Adding or or 1 occurence of Schema name before procedure.

Looks like below

CREATE OR REPLACE PROCEDURE OPS$ABC.PROCEDURE_NAME.

Thanks,

Khanna

Answer 3

C# version:

var pattern =
@"(?xi)                                # Ignore white spaces, enable comments and case-insensitive match
CREATE\s+                              # Match CREATE and space(s) after it
(?<or>OR\s+REPLACE)?                   # Zero or one match of OR REPLACE
(?(or)\s+)                             # If there's OR REPLACE in text, then match space
(PROCEDURE|TABLE|PACKAGE|FUNCTION)\s+  # Match one of words with one or more spaces after it
(.+\.)?\w+ 

Visual Basic:

Dim pattern =
"(?xi)                                  # Ignore white spaces, enable comments and case-insensitive match" & vbNewLine &
"CREATE\s+                              # Match CREATE and space(s) after it" & vbNewLine &
"(?<or>OR\s+REPLACE)?                   # Zero or one match of OR REPLACE" & vbNewLine &
"(?(or)\s+)                             # If there's OR REPLACE in text, then match space" & vbNewLine &
"(PROCEDURE|TABLE|PACKAGE|FUNCTION)\s+  # Match one of words with one or more spaces after it" & vbNewLine &
"(.+\.)?\w+                            # Match zero or one schema name following by name"

---

There is no knowledge that is not power.

Answer 4

 It's working fine.

 Thank you Johny.

 

Answer 5

You're welcome! Thanks for feedback!

---

There is no knowledge that is not power.