I want to add a prefix to tables and have recently written a PHP script to extract all tables in a string SQL query.
$sql = 'UPDATE festivals SET desc = "Starts from July"';
preg_match_all('/(from|into|update|table|join) (`?\w+`?)\s/i', $sql, $matches);
It works good but the only problem is that it extracts July because it does not distinguish between a SQL value and a real table name, so it assumes that July would be a table too.
Now I think the solution should be something to prevent extract what wrapped in a single or double quotation but don't know how to do that.
Your regex is better off this way:
"/((?:^select .+?(?:from|into))|^update|^table|join) (`?\w+`?)\s/I"
But I still agree with nvartolomei.
If you were more strict in your query-writing, you would wrap all your database, table and column names in backticks ` and they would be extremely easy to extract - just get the first match of a string between them: just make the backticks required instead of optional.
That said, I'm not entirely sure how your regex is matching from July since the July is not followed by whitespace...