Avatar of hydrazi
 asked on

MySQL Remove spaces from Column headings

I have a database that was created years ago by an office manager in Access.  It was then moved to MySQL at some point, but many of the column headers have spaces in their names.  This has always made things a pain in the butt.

Now, I am being allowed to get rid of these spaces.  The question is, can I do this with an SQL statement?  I have copied the entire database to a new instance and can mess with it all I want.  I want to bring all of the most current data over and change the header names... just not sure if I have to go and hand rename them all.... which is about 200 of them.

Any help is appreciated!

Avatar of undefined
Last Comment

8/22/2022 - Mon
Koen Van Wielink

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question

>>can I do this with an SQL statement?
don't believe so, not in raw SQL. There might be add-ons or IDEs that do this sort of thing but I'm not familiar with any that would do this. (I'm not familiar with many I hasten to add.)

here's an idea for discussion purposes...

There's a way in MySQL to dump objects etc to a text file.
recent example

open that (could be big) file into a worthy UTF8 editor
(I use notepad++ it's good and free, you may already have one, I'll use this as an example)
before starting make sure it is setup to use UTF8 by default.
then with care

Start by searching for things like: `cust new`
(in n++ you can "list all occurrences in current file" and scan that)
once you identify that this isn't going to cause pain later "good to go" replace `cust new` with `custnew`
this can be done one at a time or - if certain - replace all
save versions frequently
proceed with this until you believe it's worth testing.

& for testing you may want to do it in "chunks" rather than "all at once"

You could certainly look at the feasibility of this without committing yourself to it i.e. try a few edits and see what problems it might cause.

wow wish I'd seen that post before looking foolish...
Koen Van Wielink

Lol, not sure if it can be done in MySQL Paul, never used that database. It kinda hinges on the SP_rename procedure which comes standard with the software, so if MySQL doesn't have it, my solution doesn't fly.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Kim Walker

In what way are the spaces a problem?

Can you describe one of your tables that has the space problem and give an example of what you want a column header to change to?

Ok, so we have a table for customers with the following column header names:

Customer Name
GCE File Number
Customer File Number
Home Phone
Business Phone
Email Address

These are pain when trying to work with them in sql statements, PHP, and other scripting.  I would just like to get the spaces out of the way, it's bad form and stuff that would normally work just fine ends up failing because someone wasn't sure how to enclose the reference to the field in whatever language they are using.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.

See this link that shows how to change column names -
Koen Van Wielink

Hi Hydrazi,

Just searched online a bit and in MySQL you can use the following statement to rename column names:
alter table tablename change oldname newname varchar (10) 

Open in new window

So if you'd replace the SP_Rename procedure in my SQL query earlier with this statement and make sure the syntax is correct for MySQL I think you have a working solution.

So something like this:
Set @SQL = 'Alter table ''' + @table_name + ''' change ''' + @column_name + ''' ''' + @column_name_new + ' ''varchar (250)'''

Open in new window


Of course there will still be the follow-up activity of re-referencing each changed column name in scripts (sql,php etc.)..
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck

Yes, the follow up is going to be difficult, but not impossible.  I will try this out today.

The SQL is throwing errors for specific syntax, such as nocount not being a system variable and not being able to delcare @table_name.  I am going to see if I can translate this straight to MySQL.

You are going to bounce into all sorts of possible differences in this transition, the DDL/DML is possibly the easiest part.

but perhaps I should say, I think the question (spaces in fields names) has probably been answered.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.

oh, this might be of some interest: