Remove underscore from ALL column names in mysql

Question

All of the column names in my table have an underscore in them. I want to remove this underscore from every column and replace with a space. I know I can do this one column at a time with something like this (maybe), but this may just do it for the entries, not the column names:

UPDATE photos  SET caption = REPLACE(caption,'_',' ')

However, I have 70 columns and updating one at a time would take a while. Any shortcuts for this?

Do you mean your column content need to have underscores removed? Your query will only update the content, it won't have any affect on the column names at all... — Martin, Jul 02 '15 at 15:59

score 0 · Answer 1 · edited May 23 '17 at 11:43

0

Option 1 : Get MySQL to create an automatically generated SELECT statement that contains all of the column names (or however else works for you), and then use regular expressions to create a long SQL command.

For example:

column1
column_2
...

Regular Expression FIND: \([A-Za-z0-9]*\)_([A-Za-z0-9]*\) (assumes only one underscore, adjust as needed)

Regular Expression REPLACE: ALTER TABLE xyz CHANGE [\1_\2] [\1 \2] <type>;

Or, Option 2

you can iterate through the column names with something like this from : mysql, iterate through column names

DECLARE col_names CURSOR FOR
  SELECT column_name
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE table_name = 'tbl_name'
  ORDER BY ordinal_position;


select FOUND_ROWS() into num_rows;

SET i = 1;
the_loop: LOOP

   IF i > num_rows THEN
        CLOSE col_names;
        LEAVE the_loop;
    END IF;


    FETCH col_names 
    INTO col_name;     

     //do whatever else you need to do with the col name

    SET i = i + 1;  
END LOOP the_loop;

edited May 23 '17 at 11:43

Community

1
1

answered Jul 02 '15 at 16:00

rlb.usa

14,942
16
80
128

In Option 2 where you have "//do whatever..." I can't get anything to work in here. Should it be something like: `SELECT REPLACE ('_',' ');` – jonmrich Jul 02 '15 at 17:57
@jonmrich I agree with you that the answer presented at the link is funky. I just don't know enough about MySQL to answer your question - it sounds like that approach would involve executing dynamic SQL https://dev.mysql.com/worklog/task/?id=2793 – rlb.usa Jul 02 '15 at 18:59
@jonmrich Having been in your shoes before, I do know that it's been an hour and no one's got time to muck with stuff like this, so at the end of the day, all that matters is the task got done. I really do feel that building out SQL programatically would be faster than a pure SQL approach, and wanted to throw this out to you: http://regexr.com/ Unfortunately learning regular expressions can be a burden all on its own - I have found them invaluable in every day tasks even not programming related. – rlb.usa Jul 02 '15 at 19:01

score 0 · Answer 2 · answered Jul 02 '15 at 16:02

Dump a list of your table/column names into a file. Use that as the basis of a .sql script that does your 70 ALTER TABLE statements and execute that.

So if you create a file like this:

users this_column
sales that_column
products other_column

and then a Perl program like this:

while (<>) {
    chomp;
    my ($table,$oldname) = split / /;

    my $newname = $oldname;
    $newname =~ s/_//g;

    print "ALTER TABLE $table RENAME $oldname TO $newname;\n";
}

and run it like this:

perl rename.pl names.txt > rename.sql

You'll wind up with a SQL script like this:

ALTER TABLE users RENAME this_column TO thiscolumn;
ALTER TABLE sales RENAME that_column TO thatcolumn;
ALTER TABLE products RENAME other_column TO othercolumn;

that you can then pass to your MySQL client.

It's not doing it in SQL, but it's much less hassle than messing with the MySQL data dictionary.

Note: I haven't tested the output to see if the actual MySQL generated works. It's merely illustration.

Remove underscore from ALL column names in mysql

2 Answers2