note 62221 deleted from function.preg-match by colder
| From: | colder@php.net | Date: | Mon, 18 Jun 2007 18:29:11 +0000 |
| Subject: | note 62221 deleted from function.preg-match by colder | ||
| References: | 1 | Groups: | php.notes |
| Request: | Send a blank email to php-notes+get-127619@lists.php.net to get a copy of this message | ||
Note Submitter: jpittman2 at gmail dot com
----
Here's an extremely complicated regular expression to match the various parts of an Oracle 8i
SELECT statement. Obviously the SELECT statment is contrived, but hopefully this RegExp will work
on just about any valid SELECT statement. If there are any problems, feel free to comment.
<?
$sql = "SELECT /*+ ORDERED */ UNIQUE foo, bar, baz FROM fly, flam"
. " WHERE bee=1 AND fly=3"
. " START WITH foo=1 CONNECT BY some_condition"
. " CONNECT BY some_other_condition"
. " GROUP BY a_bunch_of_fields HAVING having_clause"
. " CONNECT BY some_other_connect_by_condition"
. " INTERSECT (SELECT * FROM friday WHERE beep=1)"
. " ORDER BY one, two, three"
. " FOR UPDATE bee bop boo";
$re = "/^ # Match beginning of string
SELECT\\s+ # SELECT
(?:(?P<hints>\\/\\*\\+\\s+.+\\s+\\*\\/|--\\+\\s+.+\\s+--)\\s+)? # Hints
(?:(?P<DUA>DISTINCT|UNIQUE|ALL)\\s+)? # (self-explanatory)
(?P<fields>.+?) # fields
\\s+FROM\\s+ # FROM
(?:(?P<tables>.+?) # tables
(?:\\s+WHERE\\s+(?P<where>.+?))? # WHERE Clauses
(?:\\s+
(?:(?:START\\s+WITH\\s(?P<startWith>.+?)\\s+)? # START WITH
CONNECT\\s+BY\\s+(?P<connectBy>.+?) # CONNECT BY
) # Hierarchical Query
|
(?:GROUP\\s+BY\\s+(?P<groupBy>.+?) # Group By
(?:\\s+HAVING\\s+(?P<having>.+?))?) # Having
)* # Hier,Group
(?:\\s+(?P<UIM>UNION(?:\\s+ALL)?|INTERSECT|MINUS) # UNION,INTSECT,MINUS
\\s+\\((?P<subquery>.+)\\))? # UIM subquery
(?:\\s+ORDER\\s+BY\\s+(?P<orderBy>.+?))? # Order by
(?:\\s+FOR\\s+UPDATE\\s*(?P<forUpdate>.+?)?)? # For Update
) # tables
$ # Match end of string
/xi";
$matches = array();
preg_match($re, $sql, $matches);
var_dump($matches);
?>