#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2017
    Posts
    5
    Rep Power
    0

    Question Regex to find brackets in a SELECT/UNION SQL Query.


    I have a problem where a database upgrade requires
    brackets in a SQL Query.

    I want to use regex to find sql queries in a file that
    do not have brackets that need to be modified.

    Example:
    Correct:
    (SELECT x FROM y)
    UNION
    (SELECT x FROM y)
    UNION
    (SELECT x FROM y);

    Incorrect:
    SELECT x FROM y
    UNION
    SELECT x FROM y
    UNION
    SELECT x FROM y;

    I want the regex to match the "Incorrect" one.
    It also must handle a unknown amount of UNION joins.

    The requirements are:
    Must have no brackets
    Must have UNION
    Must have SELECT and FROM on same line
    Must have ; at very end.

    Here is the regex I have come up with so far,
    It does not handle X/Unknown amount of UNIONS though.
    ^[^\(].*(SELECT|FROM).*(?<!\))$
    .*(UNION).*
    ^[^\(].*(SELECT|FROM).*(?<!\));$
  2. #2
  3. Wiser? Not exactly.
    Devshed God 2nd Plane (6000 - 6499 posts)

    Join Date
    May 2001
    Location
    Bonita Springs, FL
    Posts
    6,124
    Rep Power
    4103
    If you're just wanting to locate where they queries are so you can go in and fix them, then I'd just do a search for something like
    Code:
    UNION(\s+ALL)?[^(]SELECT
    That would identify every place where a UNION is followed by SELECT without a ( in-between. It also covers UNION ALL if you have any of those.
    Recycle your old CD's



    If I helped you out, show some love with some reputation, or tip with Bitcoins to 1N645HfYf63UbcvxajLKiSKpYHAq2Zxud
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2017
    Posts
    5
    Rep Power
    0
    Thank you, very helpful, still on a learning curve.

IMN logo majestic logo threadwatch logo seochat tools logo