Regex to parse a MySQL query for JOIN's

Question

I am using regex to parse a MySQL query for JOIN's, currently using this:

if (preg_match_all('/\s(LEFT|INNER|OUTER|RIGHT)?\sJOIN\s(.*)/i', $sql, $matches)) {
    print_r($matches); exit;
}

This doesn't quite work as I want an array of strings like this:

[0] => formats ON articles.article_format_id = formats.format_id
[1] => article_categories ON articles.article_id = article_categories.article_id

My current regex returns this:

Array
(
    [0] => Array
        (
            [0] =>  INNER JOIN formats ON articles.article_format_id = formats.format_id INNER JOIN article_categories ON articles.article_id = article_categories.article_id 
        )

    [1] => Array
        (
            [0] => INNER
        )

    [2] => Array
        (
            [0] => formats ON articles.article_format_id = formats.format_id INNER JOIN article_categories ON articles.article_id = article_categories.article_id 
        )

)

Any ideas?

What about: UPDATE foo SET bar='join me in my regex quest!'? :) — Bart Kiers
– Bart Kiers, Commented Feb 21, 2011 at 11:07
@Bart Kiers: Missing white spaces: UPDATE foo SET bar=' join me! ' — hsz
– hsz, Commented Feb 21, 2011 at 11:09

morja · Accepted Answer · 2011-02-21 11:43:40Z

4

The greedy .* might take everything to the end. So change the regex to:

/(?:LEFT|INNER|OUTER|RIGHT)?\sJOIN\s((?:(?!(?:LEFT|INNER|OUTER|RIGHT)?\sJOIN).)+)/i

See here: rubular

And you might have to use preg_match_all to find all matches.

edited Feb 21, 2011 at 11:43

answered Feb 21, 2011 at 11:20

morja

8,5703 gold badges43 silver badges62 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

fire Over a year ago

thanks, almost works! take a look at pastebin.com/pfdNTzvS if you run that code the last element in the $matches array [2] is what I am looking for however it leaves 'INNER' on the end of the first string! can this be improved upon at all?

morja Over a year ago

Ok, I change it, so it does omit the INNER and other prefixes.

Collectives™ on Stack Overflow

Regex to parse a MySQL query for JOIN's

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related