Get the SELECT block of a SQL Query using regex in PHP

Question

I want to get the SELECT block of an SQL query, and what I'm thinking is getting the text between the words SELECT and FROM. What is the REGEX I'm supposed to use?

Is it possible to extract the FROM, WHERE and JOIN blocks in the same manner?

UPDATE

I cannot use the http://code.google.com/p/php-sql-parser/ because it is built for MySQL and my MSSQL queries break it. I've searched a lot for an MSSQL parser and couldn't find a ready made solution so the only way I can think of is the parse the SQL myself using regex and work on the principle that the select block is between a SELECT and FROM keyword.

EXAMPLE

For the query

SELECT STRAIGHT_JOIN a,b,c 
FROM some_table an_alias
WHERE d > 5;

I want something like this to be returned:

$result['SELECT'] => 'STRAIGHT_JOIN a,b,c';
$result['FROM'] => 'some_table an_alias';
$result['WHERE'] => 'd>5';

It's unclear what you want, please add 1 example. – Ionut Flavius Pogacian Aug 20 '12 at 08:03 — Ionut Flavius Pogacian, Aug 20 '12 at 08:03

score 3 · Accepted Answer · answered Aug 20 '12 at 08:04

You could use a SQL parser library to do that for you.

Checkout this project http://code.google.com/p/php-sql-parser/

Sample input:

SELECT STRAIGHT_JOIN a,b,c 
  from some_table an_alias
 WHERE d > 5;

Sample output:

Array
( 
[OPTIONS] => Array
    (
        [0] => STRAIGHT_JOIN
    )       

[SELECT] => Array
    (
        [0] => Array
            (
                [expr_type] => colref
                [base_expr] => a
                [sub_tree] => 
                [alias] => `a`
            )

        [1] => Array
            (
                [expr_type] => colref
                [base_expr] => b
                [sub_tree] => 
                [alias] => `b`
            )

        [2] => Array
            (
                [expr_type] => colref
                [base_expr] => c
                [sub_tree] => 
                [alias] => `c`
            )

    )

[FROM] => Array
    (
        [0] => Array
            (
                [table] => some_table
                [alias] => an_alias
                [join_type] => JOIN
                [ref_type] => 
                [ref_clause] => 
                [base_expr] => 
                [sub_tree] => 
            )

    )

[WHERE] => Array
    (
        [0] => Array
            (
                [expr_type] => colref
                [base_expr] => d
                [sub_tree] => 
            )

        [1] => Array
            (
                [expr_type] => operator
                [base_expr] => >
                [sub_tree] => 
            )

        [2] => Array
            (
                [expr_type] => const
                [base_expr] => 5
                [sub_tree] => 
            )

    )

)

The only reason I cannot use this parser is because it's optimized for MySQL and my queries are MSSQL, and the results come broken... — Loupax, Aug 20 '12 at 08:06
I believe php-sql-parser should support be configured somehow for other SQL dialects: `If using another database dialect, then you may want to change the reserved words - see the ParserManual` — Stelian Matei, Aug 20 '12 at 08:11

score 2 · Answer 2 · answered Aug 20 '12 at 08:03

Have a look at this link:

http://code.google.com/p/php-sql-parser/

Example Output

Example Query

SELECT STRAIGHT_JOIN a,b,c 
  from some_table an_alias
 WHERE d > 5;

Example Output (via print_r)

Array
( 
    [OPTIONS] => Array
        (
            [0] => STRAIGHT_JOIN
        )       

    [SELECT] => Array
        (
            [0] => Array
                (
                    [expr_type] => colref
                    [base_expr] => a
                    [sub_tree] => 
                    [alias] => `a`
                )

            [1] => Array
                (
                    [expr_type] => colref
                    [base_expr] => b
                    [sub_tree] => 
                    [alias] => `b`
                )

            [2] => Array
                (
                    [expr_type] => colref
                    [base_expr] => c
                    [sub_tree] => 
                    [alias] => `c`
                )

        )

    [FROM] => Array
        (
            [0] => Array
                (
                    [table] => some_table
                    [alias] => an_alias
                    [join_type] => JOIN
                    [ref_type] => 
                    [ref_clause] => 
                    [base_expr] => 
                    [sub_tree] => 
                )

        )

    [WHERE] => Array
        (
            [0] => Array
                (
                    [expr_type] => colref
                    [base_expr] => d
                    [sub_tree] => 
                )

            [1] => Array
                (
                    [expr_type] => operator
                    [base_expr] => >
                    [sub_tree] => 
                )

            [2] => Array
                (
                    [expr_type] => const
                    [base_expr] => 5
                    [sub_tree] => 
                )

        )

)

F.P · Answer 3 · 2012-08-20T08:46:14.057

2

$matches = array();
$sql = 'SELECT STRAIGHT_JOIN a,b,c FROM some_table an_alias WHERE d > 5;';
preg_match_all('/SELECT(.+?)FROM(.+?)(?:JOIN(.+?))*WHERE(.+)/gis', $sql, $matches);

Will procude:

[0] => Array
    (
        [0] => SELECT STRAIGHT_JOIN a,b,c FROM some_table an_alias WHERE d > 5;
    )

[1] => Array
    (
        [0] =>  STRAIGHT_JOIN a,b,c 
    )

[2] => Array
    (
        [0] =>  some_table an_alias 
    )

[3] => Array
    (
        [0] => 
    )

[4] => Array
    (
        [0] =>  d > 5;
    )

edited Aug 20 '12 at 08:46

answered Aug 20 '12 at 08:04

F.P

17,421
34
123
189

This looks like a step in the right direction. I tried preg_match_all("/SELECT(.+?)FROM(.+?)(?:JOIN(.+?))*WHERE(.+)/i", $sql, $arr); print_r($arr); and got a number of empty arrays though... – Loupax Aug 20 '12 at 08:18

Get the SELECT block of a SQL Query using regex in PHP

3 Answers3

Linked