regex101: PCRE2 (PHP) Match Workspace

Group 1(constraint\s+\w+\s+)*

*repeats the group contents below any number of times:

constraintthe literal text constraint (case insensitive)

\s+any whitespace character, repeated at least once

\w+any word character, repeated at least once

\s+any whitespace character, repeated at least once

primarythe literal text primary (case insensitive)

\s+any whitespace character, repeated at least once

keythe literal text key (case insensitive)

\s+any whitespace character, repeated at least once

Group 2(((on\s+conflict\s+\w+).*?\s+(autoincrement))|((autoincrement).*?\s+(on\s+conflict\s+\w+)))

1st Alternative((on\s+conflict\s+\w+).*?\s+(autoincrement))

Group 3((on\s+conflict\s+\w+).*?\s+(autoincrement))

Group 4(on\s+conflict\s+\w+)

onthe literal text on (case insensitive)

\s+any whitespace character, repeated at least once

conflictthe literal text conflict (case insensitive)

\s+any whitespace character, repeated at least once

\w+any word character, repeated at least once

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

\s+any whitespace character, repeated at least once

Group 5(autoincrement)

autoincrementthe literal text autoincrement (case insensitive)

2nd Alternative((autoincrement).*?\s+(on\s+conflict\s+\w+))

Group 6((autoincrement).*?\s+(on\s+conflict\s+\w+))

Group 7(autoincrement)

autoincrementthe literal text autoincrement (case insensitive)

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

\s+any whitespace character, repeated at least once

Group 8(on\s+conflict\s+\w+)

onthe literal text on (case insensitive)

\s+any whitespace character, repeated at least once

conflictthe literal text conflict (case insensitive)

\s+any whitespace character, repeated at least once

\w+any word character, repeated at least once

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

Search reference

Regular Expression

(constraint\s+\w+\s+)*primary\s+key\s+(((on\s+conflict\s+\w+).*?\s+(autoincrement))|((autoincrement).*?\s+(on\s+conflict\s+\w+)))

New

CREATE TABLE leagues_new(id INTEGER PRIMARY KEY ON CONFLICT ABORT AUTOINCREMENT, name TEXT, drafttype INTEGER, scoringtype INTEGER, roundvalues INTEGER, leaguetype TEXT,  salary INTEGER, benchplayers INTEGER)



    PRIMARY KEY ON CONFLICT ABORT present and first, AUTOINCREMENT present and second

    PRIMARY KEY AUTOINCREMENT present and first, ON CONFLICT ABORT present and second

    PRIMARY KEY ON CONFLICT ABORT pop present, AUTOINCREMENT not present

    PRIMARY KEY AUTOINCREMENT present, ON CONFLICT ABORT not present

    PRIMARY KEY ON CONFLICT ABORT not present, AUTOINCREMENT not present.