RegEx to extract "SELECT [whatever] FROM INSERTED"

Question

I'm trying to pick SELECT statements out of a block of text (e.g., a trigger).

All the SELECT statements will be of the form:

SELECT (.+) FROM INSERTED

In other words, i need all the SELECT statements from the a table called INSERTED.

Research Effort

I try the obvious regex:

SELECT (.+?) FROM INSERTED

This works for: (https://regex101.com/r/4FWbIx/1)

SELECT 'Hello, world!' FROM INSERTED i

But it doesn't handle: (https://regex101.com/r/4FWbIx/2)

   SELECT⏎
      TOP(10)⏎
      CustomerID,⏎
      Name,⏎
      Address⏎
   FROM INSERTED i

because there is no SPACE after SELECT and before FROM.

Simple fix, remove the spaces; while being sure that single-line mode (s) is enabled: (https://regex101.com/r/4FWbIx/3)

SELECT(.+)FROM INSERTED

And while that works for most cases:

But it falls over dead with there is the stray word SELECT anywhere in there:

Sandbox where you can test: https://regex101.com/r/4FWbIx/4

Answer 1

You can search for all the characters that does not have 'SELECT' next to it until the cursor reaches 'FROM' as below

SELECT((?:(?!SELECT).)*)FROM INSERTED

Demo

---

If you don't want to capture spaces along with column names, use below.

SELECT\s+((?:(?!SELECT).)*)\s+FROM INSERTED

Demo

---

Edit: Changed the position of dot to make it tampered greedy token. Thanks Wiktor for pointing out this.