Rename all columns in a table removing whitespace

Question

I have a table which I imported from a very large CSV which has over 100 columns. I've just noticed they've imported with spaces in the column names.

Is there a way to rename all columns and remove the spaces?

Answer 1

The following query will remove all whitespace from column names containing any white space in the table your_table in the database your_database. You can replace with the values you need.

SELECT
    CONCAT(
           'ALTER TABLE ', C.TABLE_NAME, ' CHANGE `', 
           C.COLUMN_NAME, '` ', REPLACE(C.COLUMN_NAME, ' ', ''), ' ',
           C.DATA_TYPE, ';'
          )
FROM
    INFORMATION_SCHEMA.COLUMNS C
WHERE
    TABLE_SCHEMA = 'your_database' AND TABLE_NAME = 'your_table'
    AND C.COLUMN_NAME LIKE '% %';

Pay very close attention to the backticks which surround the column name. This will output a set of ALTER TABLE statements which look like the following:

ALTER TABLE your_table CHANGE `Old Column Name` OldColumnName VARCHAR;