split select statement with regular expression

Question

hello, i need to split any mysql select statement in its main parts: SELECT, FROM, ALL THE JOINS(if there are any), WHERE(if it exists),GROUP BY(if it exists), HAVING(if it exists), ORDER BY(if it exists), LIMIT(if it exists)...

i tried using regular expressions, but i'm not very good with them... for the SELECT the regex was simple (any SELECT that comes at the begining of the string-^SELECT), but after that i stumbled... all i know it's that the 'FROM' must not be contained in () - which will mean it's from a subselect... and i think that all the other parts of the select (JOINS, WHERE, GROUP BY, etc) will match this rule... but i don't know how to write such a rule... and i hope someone can help me...

for the select statement:

SELECT SQL_CALC_FOUND_ROWS (SUM(TIME_TO_SEC(audioMelodii.durata))/60) AS durataTotal, (SELECT COUNT(audioMelodii.id) FROM audioMelodii) AS nrMelodii, audioArtisti.nume AS artist, audioAlbume.*
FROM audioAlbume
LEFT OUTER JOIN audioMelodiiAlbume ON (audioMelodiiAlbume.idAlbum=audioAlbume.id)
LEFT OUTER JOIN audioMelodii ON (audioMelodii.id=audioMelodiiAlbume.idMelodie)
LEFT OUTER JOIN audioArtisti ON (audioAlbume.idArtist=audioArtisti.id)
WHERE audioAlbume.idArtist='$idArtist'
GROUP BY audioAlbume.id
ORDER BY dataLansare DESC, id DESC
LIMIT 0,10

the result i'm looking for would be something like this:

$STATEMENT['SELECT']='SELECT SQL_CALC_FOUND_ROWS (SUM(TIME_TO_SEC(audioMelodii.durata))/60) AS durataTotal, (SELECT COUNT(audioMelodii.id) FROM audioMelodii) AS nrMelodii, audioArtisti.nume AS artist, audioAlbume.*';
$STATEMENT['FROM']='FROM audioAlbume';
$STATEMENT['JOINS']='LEFT OUTER JOIN audioMelodiiAlbume ON (audioMelodiiAlbume.idAlbum=audioAlbume.id)
LEFT OUTER JOIN audioMelodii ON (audioMelodii.id=audioMelodiiAlbume.idMelodie)
LEFT OUTER JOIN audioArtisti ON (audioAlbume.idArtist=audioArtisti.id)';
$STATEMENT['WHERE']="WHERE audioAlbume.idArtist='$idArtist'";

etc...

thank you very much! all the best!

Answer 1

SQL is not a language that can be parsed with regular expressions.
You really need to use a full-blown SQL parser for this task.

See also:

• "Regex to Extract Update Columns from a Sql Statement."
• "Parsing SQL string"

Answer 2

You can't do this easily with regular expressions due to the recursive complexity of the SQL (sub-queries, sub-sub-queries, which can occur in several places.

Maybe if you explain your ultimate objective we could help you find a better solution.

Answer 3

thank you for your answers!

i don't want to parse the subqueries.. all i want is to split the statement in an array that contains only the main parts (SELECT,FROM,JOINS,WHERE,etc)... if any of these parts contains subqueries this should not affect the regex.

i think that if i split the statement by any of those words (SELECT,FROM,JOINS,WHERE,etc) that aren't in parenthesis, it would be ok... but i don't know how to do this...

my objective is to have an array with those parts so that i can verify if a specific part exists in a query...

thank you! all the best!