PHP Regular Expression Substring

Question

I have a very long query with a lot of sub-queries in it (almost a thousand lines long).

What I want to do is to get the names of all the tables that are being used in the query. So take the following example of a query just to get an idea:

SQL Query

$sql = "SELECT
    ...,
    ...,
    (
        SELECT COUNT(t.id) AS t_count
        FROM _user_just_a_table_db_ jat
        WHERE ...
        AND ...
        AND ...
    ),
    ...,
    ...,
    ...
    FROM _user_main_table_db_ tbl
    LEFT JOIN _user_joining_table_db_ jt
    ON jt.id = tbl.jt_id
    WHERE ...
    AND ...
    AND tbl.id IN (
        SELECT at.id
        FROM _user_another_table_db_ at
        WHERE at.active = 'Y'
    )";

In this particular situation, I want to get maybe an array or a string with the following results:

just_a_table
main_table
joining_table
another_table

Notes

Please note that the names of the table can appear multiple times. You may or may not worry about that as I can easily remove duplicates after getting the resulting array or string.
As you may have already noticed, the list of strings that I want to get will ALWAYS be in between _user_ and _db_.

@rock321987 (_user_.*?_db_) would get the full table name. — apokryfos
– apokryfos, Commented Apr 5, 2016 at 15:23
@rock321987 I'm using your regex but I'm getting an error that says Warning: preg_match(): Unknown modifier '('. Here's my code: preg_match('_user_(.*?)_db_/gm', $sql, $matches);. — dokgu
– dokgu, Commented Apr 5, 2016 at 15:26

rock321987 · Accepted Answer · 2016-04-05 15:30:49Z

2

This should work

_user_(.*?)_db_

Regex Demo

PHP code

$match = array();
$re = "/_user_(.*?)_db_/"; 
$str = "SELECT\n    ...,\n    ...,\n    (\n        SELECT COUNT(t.id) AS t_count\n        FROM _user_just_a_table_db_ jat\n        WHERE ...\n        AND ...\n        AND ...\n    ),\n    ...,\n    ...,\n    ...\n    FROM _user_main_table_db_ tbl\n    LEFT JOIN _user_joining_table_db_ jt\n    ON jt.id = tbl.jt_id\n    WHERE ...\n    AND ...\n    AND tbl.id IN (\n        SELECT at.id\n        FROM _user_another_table_db_ at\n        WHERE at.active = 'Y'\n    )\""; 

$res = preg_match_all($re, $str, $match);
print_r($match[1]);

Ideone Demo

edited Apr 5, 2016 at 15:30

answered Apr 5, 2016 at 15:25

rock321987

11.1k1 gold badge34 silver badges44 bronze badges

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

1 Comment

rock321987 Over a year ago

@chris85 actually I generated the code from regex101..Updated

Infor Mat · Accepted Answer · 2016-04-05 15:27:34Z

0

$pattern = '/_user_(.*)_db_/';
preg_match_all($pattern, $sql, $match);
var_dump($match[0]);

answered Apr 5, 2016 at 15:27

Infor Mat

1173 silver badges14 bronze badges

1 Comment

JAL Over a year ago

Please elaborate on how this code answers the question (this answer was in the Low Quality Posts review queue).

Collectives™ on Stack Overflow

PHP Regular Expression Substring

SQL Query

Notes

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

SQL Query

Notes

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related