Match specific pattern only up until character using RegEx

user3jCm 0
Viewed 41

I am trying to create a RegEx to get the database, schema and table names from an SQL CREATE TABLE statement:

Example 1:

CREATE TABLE "finance"."invoices_1" (
    "abc" NUMBER(15,0),
    "def" VARCHAR2(200),
    "ghi" DATE
);

Example 2:

CREATE TABLE "commerce"."finance"."invoices_1" (
    "abc" NUMBER(15,0),
    "def" VARCHAR2(200),
    "ghi" DATE
);

The depth of the schema varies, so I'm trying to come up with a regular experession that match the names, whether they include the database name, schema name or only the table name:

\"[A-Za-z0-9_]*\"

Unfortunately, this also matches the column names. Is there a way to match this specific pattern only up until the first opening bracket?

sql regex
1 Answers

Try this:

"\w+"(?:\."\w+"){0,2}(?=\s*\()

  1. "\w+" -- any word text inside the double quotes (f.e. "commerce")
  2. (?:\."\w+"){0,2} -- optionally up to 2 times extended table name part (f.e. ."finance"."invoices_1" or ."invoices_1")
  3. (?=\s*\() -- positive lookahead to look for the opening bracket symbol (()

Demo: https://regex101.com/r/0F6egD/1

star670
Related