regex101: PCRE (Legacy) Match Workspace

Group prepend(?<prepend> (?> \s++ | #multi-line comment by Jeffrey Friedl (fastest) /\* [^*]* \*+ (?:[^*/][^*]*\*+)* / | ;++ )*+ )

Atomic Group(?> \s++ | #multi-line comment by Jeffrey Friedl (fastest) /\* [^*]* \*+ (?:[^*/][^*]*\*+)* / | ;++ )*+

*+repeats the group contents below any number of times:

1st Alternative \s++

\s++any whitespace character, repeated at least once

2nd Alternative #multi-line comment by Jeffrey Friedl (fastest) /\* [^*]* \*+ (?:[^*/][^*]*\*+)* /

Comment:multi-line comment by Jeffrey Friedl (fastest)

/the literal text /

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

Negated Character Class[^*]*

*matches any number of characters outside the set below:

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

\*+the literal * (42₁₀ / 52₈ / 2A₁₆) , repeated at least once

Non-Capturing Group(?:[^*/][^*]*\*+)*

*repeats the group contents below any number of times:

Negated Character Class[^*/]

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

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

Negated Character Class[^*]*

*matches any number of characters outside the set below:

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

\*+the literal * (42₁₀ / 52₈ / 2A₁₆) , repeated at least once

/ the literal text /

3rd Alternative ;++

;++the literal ; (59₁₀ / 73₈ / 3B₁₆) , repeated at least once

Comment:https://www.postgresql.org/docs/11/sql-commands.html

1st Alternativeabort

abortthe literal text abort (case insensitive)

2nd Alternativealter

alterthe literal text alter (case insensitive)

3rd Alternativebegin

beginthe literal text begin (case insensitive)

4th Alternativecache

cachethe literal text cache (case insensitive)

5th Alternativecall

callthe literal text call (case insensitive)

6th Alternativecheckpoint

checkpointthe literal text checkpoint (case insensitive)

7th Alternativecluster

clusterthe literal text cluster (case insensitive)

8th Alternativecomment

commentthe literal text comment (case insensitive)

9th Alternativecommit

committhe literal text commit (case insensitive)

10th Alternativecopy

copythe literal text copy (case insensitive)

11th Alternativecreate

createthe literal text create (case insensitive)

12th Alternativedeallocate

deallocatethe literal text deallocate (case insensitive)

13th Alternativedelete

deletethe literal text delete (case insensitive)

14th Alternativediscard

discardthe literal text discard (case insensitive)

15th Alternativedo

16th Alternativedrop

17th Alternativeexecute

18th Alternativeflush

19th Alternativegrant

20th Alternativeimport\s++foreign\s++schema

21st Alternativeinsert

22nd Alternativekill

23rd Alternativeload

24th Alternativelock

25th Alternativeprepare

26th Alternativeprepare\s++transaction

27th Alternativereassign\s++owned

28th Alternativerefresh\s++materialized\s++view

29th Alternativerelease\s++savepoint

30th Alternativerename

31st Alternativereplace

32nd Alternativereset

33rd Alternativerevoke

34th Alternativerollback

35th Alternativeselect\s++into

36th Alternativeset\s++constraints

37th Alternativestart\s++transaction

38th Alternativetruncate

39th Alternativeupdate

^start of line

(?&prepend)calls group prepend as a subroutine

\Kresets the starting point of the reported match. Any previously consumed characters are no longer included in the final match

(?&command)calls group command as a subroutine

\ban ASCII word boundary

Negative Lookahead(?! (?&prepend) \( #REPLACE() function can be similar to command )

Global pattern flags

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

x modifier: extended. Ignores unescaped whitespace and comments outside character classes

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

X modifier: eXtra. A \ followed by a letter with no special meaning causes an error

s modifier: single line. Allows a dot to match line terminators

Search reference

Regular Expression

(?(DEFINE)
  (?<prepend>
    (?>
         \s++
      |  #multi-line comment by Jeffrey Friedl (fastest)
         /\*
           [^*]* \*+
           (?:[^*/][^*]*\*+)*
         /
      |  ;++
    )*+
  )
  #https://www.postgresql.org/docs/11/sql-commands.html
  (?<command>abort|alter|begin|cache|call|checkpoint|cluster|comment|commit|copy|create|deallocate|delete|discard|do|drop|execute|flush|grant|import\s++foreign\s++schema|insert|kill|load|lock|prepare|prepare\s++transaction|reassign\s++owned|refresh\s++materialized\s++view|release\s++savepoint|rename|replace|reset|revoke|rollback|select\s++into|set\s++constraints|start\s++transaction|truncate|update)
)
^
(?&prepend)
\K
(?&command)
\b
(?!
  (?&prepend)
  \(  #REPLACE() function can be similar to command
)

Test String
New

WITH t1 AS (
  -- UPDATE 
  /* INSERT */
  SELECT * 
     FROM t
     WHERE s = 
           REPLACE(s, 'a', 'b')
),
t2 AS (
  -- комментарий UPDATE
  /* INSERT
*/UPDATE--
	* FROM t SET i = 1
  RETURNING *
),
  
INSERT b
SELECT * FROM t2

Regular Expressions 101

Regular Expression

Test String
New

Extraction
Processing...

Regular Expression

Test StringNew

ExtractionProcessing...

Test String
New

Extraction
Processing...