Match string with Regex as long as it is not surrounded by parentheses

Question

I am looking to match a string "Order By XXX" where XXX can be any letter, number, period, comma, space or square bracket. However, I would only like to match this if it is not surrounded by parentheses (parentheses on one side is ok, as long as it it not on both sides). So it should match the part in italics from "", by it should not match anything in

Should match (matched section in italics):

• Select X from Y order by z
• Select y = (select top 1 Z from C Order by [ID] desc)

Should not match:

• Select X from Y (order by z)
• Select a.a, NTILE(4) OVER (Order by a.b) group by a.c

I have the regex string for matching the order by text: [ ]*order by [\w,.\[\] ]+. However, I am having some trouble getting the lookahead/behind the work properly. Any advice on how to proceed?

Answer 1

Try this:

(?<!\(\s*)order\s+by\s+[\w,.\[\] ]+(?<!\s*\))

When tested in PowerShell:

PS> @(
    'Select X from Y order by z'
    'Select y = (select top 1 Z from C Order by [ID] desc)'
    'Select X from Y (order by z)'
    'Select a.a, NTILE(4) OVER (Order by a.b) group by a.c'
    'Order by 87'
    '(Order by 87)'
    '( Order by 87 )'
    '(Order by 87 )'
    '( Order by 87)'
    'Order by _foo'
) -match '(?<!\(\s*)order\s+by\s+[\w,.\[\] ]+(?<!\s*\))'

Select X from Y order by z
Select y = (select top 1 Z from C Order by [ID] desc)
Order by 87
Order by _foo

PS>