#PHP Heredoc syntax $regex = <<< END_OF_REGEX / [^ \"'`(),]*\([^)]*\) #match functions like concat(x,"y",`a`.`z`) or sum(`xyz`); |\([^)]*?\) #match grouped items |"[^"]*?" #match double quoted items |'[^']*?' #match single quoted items |`[A-Za-z0-9_ .'\"()+\\-&^%\$+?%\\/\\\\!`]+` #match backtick mysql names |[^ ,]+ #match keywords, operators and aliases |, /xi END_OF_REGEX;
EDIT: After some comments and further testing, I've modified the regex to look like the following:
$regex = <<<'END_OF_REGEX' / [A-Za-z_.]+\(.*?\)+ # Match FUNCTION(...) |\(.*?\)+ # Match grouped items |"(?:[^"]|\"|"")*"+ # Match double quotes |'[^'](?:|\'|'')*'+ # and single quotes |`(?:[^`]|``)*`+ # and backticks |[^ ,]+ |, /x END_OF_REGEX;
This will properly match escaped values within quoted statements. Functions with nested function calls or subselects may not end up being fully grouped together. If anybody has any idea how I might fix that, well, feel free to chime in :D
When used with the preg_match_all() function, an array is produced which represents the tokenized string. Functions calls, quoted strings and grouped expressions are returned together, and may need additional processing, depending on your needs.
-- select sum(a * b) sweety,'abc' as a1, -- "abc",concat(`def`.`abc`,'hello', x.y, z) as `blah`, -- null + 1 -- from `abc`.`def` as def1 -- join xyz.zzz z1 -- on (z1.a = def1.a) Array (  => select  => sum(a * b)  => sweety  => ,  => 'abc'  => as  => a1  => ,  => "abc"  => ,  => concat(`def`.`abc`,'hello', x.y, z)  => as  => `blah`  => ,  => null  => +  => 1  => from  => `abc`.`def`  => as  => def1  => join  => xyz.zzz  => z1  => on  => (z1.a = def1.a) )
I'm using this to process SQL statements and automatically create the Flexviews calls for the query.
The following output is generated by my program for the above SQL statement (only the SELECT clause is currently represented):
call flexviews.add_expr(@mvid, 'SUM', 'a * b', 'sweety'); call flexviews.add_expr(@mvid, 'GROUP', 'abc', '1'); call flexviews.add_expr(@mvid, 'GROUP', "abc", '_abc_'); call flexviews.add_expr(@mvid, 'GROUP', 'concat(`def`.`abc`,\'hello\', x.y, z)', 'blah'); call flexviews.add_expr(@mvid, 'GROUP', 'null + 1', 'null_+_1');