CODEWITHSUNDEEP
mysql
Jonas Borneland
Jonas Borneland

Reputation: 413

Rename the prefix of multiple MySQL tables

I've searched for a solution and found something that seem promising (I pasted it in the phpmyadmin SQL tab):

SELECT Concat('RENAME TABLE ', TABLE_NAME, ' TO toersten_', TABLE_NAME, ';') 
FROM information_schema.tables
WHERE table_name like 'jos_%'
and table_schema='mydb_test_toersten'

But it doesn't work. Not errors, but the prefix also seem to be placed in the wrong spot. Here's the result after executing:

RENAME TABLE jos_virtuemart_coupons TO toersten_jos_virtuemart_coupons;

Here's what I want to happen:

All tables with the prefix "jos_" needs to be renamed to "toersten_" So the table "jos_myexample_table" would become "toersten_myexample_table"

And this needs to be done on a lot of tables. So I can't manually enter all the table names.

Upvotes: 0

Views: 428

Answers (1)

Madhur Bhaiya
Madhur Bhaiya

Reputation: 28834

You can use Substring_Index() function to get the substring after jos_. -1 would mean that you will extract the right side substring after jos_: 

SELECT Concat('RENAME TABLE ', 
              TABLE_NAME, 
              ' TO toersten_', 
              SUBSTRING_INDEX(TABLE_NAME, 'jos_',-1), 
              ';') 
FROM information_schema.tables
WHERE table_name like 'jos_%'
and table_schema='mydb_test_toersten'

Upvotes: 2

Related Questions