regex101: Get all table from sql query string | 从SQL查询语句中获取所有表名

1st Alternative(SELECT|DELETE)(?:\s*\/\*.*\*\/\s*?)*\s+FROM*\s+([^\s\/*;]+)?

Group 1(SELECT|DELETE)

1st AlternativeSELECT

SELECTthe literal text SELECT (case insensitive)

2nd AlternativeDELETE

DELETEthe literal text DELETE (case insensitive)

Non-Capturing Group(?:\s*\/\*.*\*\/\s*?)*

*repeats the group contents below any number of times:

\s*any whitespace character, repeated any number of times

\/the literal / (47₁₀ / 57₈ / 2F₁₆)

\*the literal * (42₁₀ / 52₈ / 2A₁₆)

.*any character (except for line terminators), repeated any number of times

\*the literal * (42₁₀ / 52₈ / 2A₁₆)

\/the literal / (47₁₀ / 57₈ / 2F₁₆)

\s*?any whitespace character, repeated any number of times

\s+any whitespace character, repeated at least once

FROthe literal text FRO (case insensitive)

M*the literal M (77₁₀ / 115₈ / 4D₁₆) (case insensitive), repeated any number of times

\s+any whitespace character, repeated at least once

Group 2([^\s\/*;]+)?

?repeats the group contents below at most once:

Negated Character Class[^\s\/*;]+

+matches at least one character outside the set below:

\sany whitespace character

\/the literal / (47₁₀ / 57₈ / 2F₁₆)

*the literal * (42₁₀ / 52₈ / 2A₁₆)

;the literal ; (59₁₀ / 73₈ / 3B₁₆)

2nd Alternative(?:(?:(CREATE|ALTER|DROP)(?:(?:\s*\/\*.*\*\/\s*?)*\s+OR(?:\s*\/\*.*\*\/\s*?)*\s+(REPLACE))?)(?:\s*\/\*.*\*\/\s*?)*\s+TABLE(?:(?:\s*\/\*.*\*\/\s*?)*\s+IF(?:\s*\/\*.*\*\/\s*?)*\s+EXISTS)?|(UPDATE)|(ALTER)|(INSERT)(?:\s*\/\*.*\*\/\s*?)*\s+(?:INTO?))(?:\s*\/\*.*\*\/\s*?)*\s+([^\s\/*;]+)

Non-Capturing Group(?:(?:(CREATE|ALTER|DROP)(?:(?:\s*\/\*.*\*\/\s*?)*\s+OR(?:\s*\/\*.*\*\/\s*?)*\s+(REPLACE))?)(?:\s*\/\*.*\*\/\s*?)*\s+TABLE(?:(?:\s*\/\*.*\*\/\s*?)*\s+IF(?:\s*\/\*.*\*\/\s*?)*\s+EXISTS)?|(UPDATE)|(ALTER)|(INSERT)(?:\s*\/\*.*\*\/\s*?)*\s+(?:INTO?))

1st Alternative(?:(CREATE|ALTER|DROP)(?:(?:\s*\/\*.*\*\/\s*?)*\s+OR(?:\s*\/\*.*\*\/\s*?)*\s+(REPLACE))?)(?:\s*\/\*.*\*\/\s*?)*\s+TABLE(?:(?:\s*\/\*.*\*\/\s*?)*\s+IF(?:\s*\/\*.*\*\/\s*?)*\s+EXISTS)?

Non-Capturing Group(?:(CREATE|ALTER|DROP)(?:(?:\s*\/\*.*\*\/\s*?)*\s+OR(?:\s*\/\*.*\*\/\s*?)*\s+(REPLACE))?)

Group 3(CREATE|ALTER|DROP)

1st AlternativeCREATE

CREATEthe literal text CREATE (case insensitive)

2nd AlternativeALTER

ALTERthe literal text ALTER (case insensitive)

3rd AlternativeDROP

DROPthe literal text DROP (case insensitive)

Non-Capturing Group(?:(?:\s*\/\*.*\*\/\s*?)*\s+OR(?:\s*\/\*.*\*\/\s*?)*\s+(REPLACE))?

?repeats the group contents below at most once:

Non-Capturing Group(?:\s*\/\*.*\*\/\s*?)*

*repeats the group contents below any number of times:

\s*any whitespace character, repeated any number of times

\/the literal / (47₁₀ / 57₈ / 2F₁₆)

\*the literal * (42₁₀ / 52₈ / 2A₁₆)

.*any character (except for line terminators), repeated any number of times

\*the literal * (42₁₀ / 52₈ / 2A₁₆)

\/the literal / (47₁₀ / 57₈ / 2F₁₆)

\s*?any whitespace character, repeated any number of times

\s+any whitespace character, repeated at least once

ORthe literal text OR (case insensitive)

Non-Capturing Group(?:\s*\/\*.*\*\/\s*?)*

*repeats the group contents below any number of times:

\s*any whitespace character, repeated any number of times

\/the literal / (47₁₀ / 57₈ / 2F₁₆)

\*the literal * (42₁₀ / 52₈ / 2A₁₆)

.*any character (except for line terminators), repeated any number of times

\*the literal * (42₁₀ / 52₈ / 2A₁₆)

\/the literal / (47₁₀ / 57₈ / 2F₁₆)

\s*?any whitespace character, repeated any number of times

\s+any whitespace character, repeated at least once

Group 4(REPLACE)

REPLACEthe literal text REPLACE (case insensitive)

Non-Capturing Group(?:\s*\/\*.*\*\/\s*?)*

*repeats the group contents below any number of times:

\s*any whitespace character, repeated any number of times

\/the literal / (47₁₀ / 57₈ / 2F₁₆)

\*the literal * (42₁₀ / 52₈ / 2A₁₆)

.*any character (except for line terminators), repeated any number of times

\*the literal * (42₁₀ / 52₈ / 2A₁₆)

\/the literal / (47₁₀ / 57₈ / 2F₁₆)

\s*?any whitespace character, repeated any number of times

\s+any whitespace character, repeated at least once

TABLEthe literal text TABLE (case insensitive)

Non-Capturing Group(?:(?:\s*\/\*.*\*\/\s*?)*\s+IF(?:\s*\/\*.*\*\/\s*?)*\s+EXISTS)?

?repeats the group contents below at most once:

Non-Capturing Group(?:\s*\/\*.*\*\/\s*?)*

*repeats the group contents below any number of times:

\s*any whitespace character, repeated any number of times

\/the literal / (47₁₀ / 57₈ / 2F₁₆)

\*the literal * (42₁₀ / 52₈ / 2A₁₆)

.*any character (except for line terminators), repeated any number of times

\*the literal * (42₁₀ / 52₈ / 2A₁₆)

\/the literal / (47₁₀ / 57₈ / 2F₁₆)

\s*?any whitespace character, repeated any number of times

\s+any whitespace character, repeated at least once

IFthe literal text IF (case insensitive)

Non-Capturing Group(?:\s*\/\*.*\*\/\s*?)*

*repeats the group contents below any number of times:

\s*any whitespace character, repeated any number of times

\/the literal / (47₁₀ / 57₈ / 2F₁₆)

\*the literal * (42₁₀ / 52₈ / 2A₁₆)

.*any character (except for line terminators), repeated any number of times

\*the literal * (42₁₀ / 52₈ / 2A₁₆)

\/the literal / (47₁₀ / 57₈ / 2F₁₆)

\s*?any whitespace character, repeated any number of times

\s+any whitespace character, repeated at least once

EXISTSthe literal text EXISTS (case insensitive)

2nd Alternative(UPDATE)

Group 5(UPDATE)

UPDATEthe literal text UPDATE (case insensitive)

3rd Alternative(ALTER)

4th Alternative(INSERT)(?:\s*\/\*.*\*\/\s*?)*\s+(?:INTO?)

Non-Capturing Group(?:\s*\/\*.*\*\/\s*?)*

\s+any whitespace character, repeated at least once

Group 8([^\s\/*;]+)

3rd Alternative(?:(REPLACE)(?:\s*\/\*.*\*\/\s*?)*\s+(?:INTO?))(?:\s*\/\*.*\*\/\s*?)*\s+([^\s\/*;]+)(?:\s*\/\*.*\*\/\s*?)*\s+([^\s\/*;]+)

Global pattern flags

g modifier: global. Finds all matches instead of stopping after the first

m modifier: multiline. Causes ^ and $ to match the start and end of each line, not only the start and end of the string

i modifier: insensitive. Matches without distinguishing uppercase and lowercase letters

25 tokens

Regular Expression

(SELECT|DELETE)(?:\s*\/\*.*\*\/\s*?)*\s+FROM*\s+([^\s\/*;]+)?|(?:(?:(CREATE|ALTER|DROP)(?:(?:\s*\/\*.*\*\/\s*?)*\s+OR(?:\s*\/\*.*\*\/\s*?)*\s+(REPLACE))?)(?:\s*\/\*.*\*\/\s*?)*\s+TABLE(?:(?:\s*\/\*.*\*\/\s*?)*\s+IF(?:\s*\/\*.*\*\/\s*?)*\s+EXISTS)?|(UPDATE)|(ALTER)|(INSERT)(?:\s*\/\*.*\*\/\s*?)*\s+(?:INTO?))(?:\s*\/\*.*\*\/\s*?)*\s+([^\s\/*;]+)|(?:(REPLACE)(?:\s*\/\*.*\*\/\s*?)*\s+(?:INTO?))(?:\s*\/\*.*\*\/\s*?)*\s+([^\s\/*;]+)(?:\s*\/\*.*\*\/\s*?)*\s+([^\s\/*;]+)

New

DROP TABLE IF EXISTS tbl1; CREATE TABLE tbl1 ...
CREATE TABLE tbl1 ...
CREATE OR REPLACE TABLE tbl1 ...
INSERT /*some comment*/ INTO tbl2 ...
INSERT /*some comment*/ INTO tbl2 ...
UPDATE tbl3 SET col1 = ...
/*some garbage comments*/ UPDATE tbl3 SET col1 = ... 
DELETE from tbl4 ...
select from select_table insert into table_ttt
alter table alter_table 
#1 some optional statements like  CREATE /*some comments*/ TABLE table_name everything else
#1 some optional statements like  CREATE /*some comments*/ OR REPLACE TABLE table_name everything else
#2 some optional statements like  INSERT /*some comments*/ INTO /*some comments*/ table_name
#3 some optional statements like  UPDATE /*some comments*/ table_name everything else

Extraction

DROP TABLE IF EXISTS tbl1; CREATE TABLE tbl1 ...
CREATE TABLE tbl1 ...
CREATE OR REPLACE TABLE tbl1 ...
INSERT /*some comment*/ INTO tbl2 ...
INSERT /*some comment*/ INTO tbl2 ...
UPDATE tbl3 SET col1 = ...
/*some garbage comments*/ UPDATE tbl3 SET col1 = ... 
DELETE from tbl4 ...
select from select_table insert into table_ttt
alter table alter_table 
#1 some optional statements like  CREATE /*some comments*/ TABLE table_name everything else
#1 some optional statements like  CREATE /*some comments*/ OR REPLACE TABLE table_name everything else
#2 some optional statements like  INSERT /*some comments*/ INTO /*some comments*/ table_name
#3 some optional statements like  UPDATE /*some comments*/ table_name everything else