Excluding a specific substring from a regex

Question

I'm attempting to mangle a SQL query via regex. My goal is essentially grab what is between FROM and ORDER BY, if ORDER BY exists.

So, for example for the query: SELECT * FROM TableA WHERE ColumnA=42 ORDER BY ColumnB it should capture TableA WHERE ColumnA=42, and it should also capture if the ORDER BY expression isn't there.

The closest I've been able to come is SELECT (.*) FROM (.*)(?=(ORDER BY)) which fails without the ORDER BY.

Hopefully I'm missing something obvious. I've been hammering in Expresso for the past hour trying to get this.

Answer 1

You need to make the whole subexpression including the ORDER BY optional, by putting a ? after it:

SELECT (.*) FROM (.*?) (ORDER BY (.*))?$

You also need the pattern after the FROM to be a non-greedy match .*?, otherwise that would always consume the ORDER BY, and the final part would never match.

Answer 2

Just add an alteration to your regex:

SELECT (.*) FROM (.*)(?=(ORDER BY|\Z|$))

Answer 3

Could you just append "ORDER BY" to the string before applying the regex? It's a bit of a hack, but it ought to work.

Answer 4

I think you were looking for this :

^SELECT (.+) FROM (.+?)( ORDER BY (.+))?$

Note that I forced the start and end points of the string. This way the optional order by is going to be evaluated, and if it's not present, the rest of the code is going to be included in the second match group.

P.S. you can of course edit it as you want.. I put the last .+ in a group as well, so that you can easily access the order by params.. if you don't need it - you can remove them :P Same with the first one in the SELECT, but I guess you know that already :)