MySQL Remove spaces from Column headings

>>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...

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.

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.

See this link that shows how to change column names -
http://php.about.com/od/learnmysql/p/alter_table.htm

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) 

Select allOpen 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)'''

Select allOpen 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.)..

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.

oh, this might be of some interest:
http://troels.arvin.dk/db/rdbms/