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
– Ionut Flavius Pogacian

2012-08-20 08:03:23 +00:00
Commented Aug 20, 2012 at 8:03 — Ionut Flavius Pogacian
– Ionut Flavius Pogacian, Commented Aug 20, 2012 at 8:03

Stelian Matei · Accepted Answer · 2012-08-20 08:04:16Z

3

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] => 
            )

    )

)

answered Aug 20, 2012 at 8:04

Stelian Matei

11.6k3 gold badges28 silver badges29 bronze badges

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

2 Comments

Loupax Over a year ago

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...

Stelian Matei Over a year ago

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

Rick Kuipers · Accepted Answer · 2012-08-20 08:03:57Z

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 · Accepted Answer · 2012-08-20 08:46:14Z

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, 2012 at 8:46

answered Aug 20, 2012 at 8:04

F.P

18k34 gold badges127 silver badges196 bronze badges

1 Comment

Loupax Over a year ago

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...

Collectives™ on Stack Overflow

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

3 Answers 3

2 Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related