PHP replace dots within backticks

Question

I have a project where I need to modify the table names of a lot tables in my database and therefore a lot of SQL queries.

Right now my tables all have names like 1.customers, 2.customers, etc I need to update these to 1_customer, 2_customers, etc.

Is it possible, through preg_replace to replace all dots within backticks with an underscore

For example I would want

"UPDATE `1.customers` SET `value` = '1.0'"

to become

"UPDATE `1_customers` SET `value` = '1.0'"

There are approximately 50 tables in all.

how many tables are we talking? and you can't just `preg_replace` since your tables are already named. doing `preg_replace` would only change the look of the queries. You need to actually change the names for them, in altering them. — Funk Forty Niner, May 01 '17 at 21:22
I realize the correct solution is to update the code, which I plan to do, I'm looking for a temporary measure while I'm in the process of doing that. — Bryan Kaczmarek, May 01 '17 at 21:25
This answer might help http://stackoverflow.com/a/10490243/1415724 or point you in the right direction. It's `mysql_` but that can easily be modified. — Funk Forty Niner, May 01 '17 at 21:28
There's also https://www.electrictoolbox.com/rename-multiple-tables-mysql/ and https://ruleant.blogspot.ca/2009/03/rename-multiple-tables-in-mysql.html and https://www.sqlservercentral.com/Forums/Topic1445241-392-1.aspx - you can continue your search *"how to rename multiple tables in php sql" *. — Funk Forty Niner, May 01 '17 at 21:30
Btw; are all tables sequential, meaning they are 1,2,3,4,5.... right up to 50'ish with no missing numbers breaking the chain? this might help others to probably figure out a php method instead and doing it dynamically. @BryanKaczmarek and what's the RDBMS, mssql, mysql, other? it could make a difference. — Funk Forty Niner, May 01 '17 at 21:32

score 1 · Answer 1 · answered May 01 '17 at 22:27

Replaces all dots within backticks with an underscore

$input = "UPDATE `1.customers` SET `value` = '1.0'";

$output = preg_replace_callback(
  '/`(.+?)`/',
  function ($matches) {
    return str_replace('.', '_', $matches[0]);
  },
  $input
);

$output

UPDATE `1_customers` SET `value` = '1.0'

score 0 · Answer 2 · answered May 01 '17 at 21:45

Use capture groups in the regular expression to match the parts before and after the dot, so you can copy them into the replacement.

$str = preg_replace_callback('/(`\d+)\.(\w+`)/', '$1_$2', $str);

This assumes there's just one dot in each name. If not, you can use preg_replace_callback() to match a name with any number of dots, and then use str_replace() in the callback function to replace all the dots with underscores.

PHP replace dots within backticks

2 Answers2