CODEWITHSUNDEEP
mysql
Joseph
Joseph

Reputation: 25

How to change a MySQL column name that has # (Hash) symbol

I have a table, some of its columns have a # symbol, for example let's name the table Students, we have some columns that look like this:

Grade#Final#Exam double

Count#Absent int(11)

etc...

I want to rename the columns to remove the #, I tried these variations without success:

alter table Students change Grade#Final#Exam GradeFinalExam double;

alter table Students change 'Grade#Final#Exam' GradeFinalExam double;

alter table Students change 'Grade\#Final\#Exam' GradeFinalExam double;

How can I do that ? Also is there a faster way to remove just the '#' from each of the columns instead of having to rename them by hand one by one ? (The table has many columns and different data types which make it hard to automate the process of renaming)

Upvotes: 0

Views: 1826

Answers (4)

shalini
shalini

Reputation: 1290

ALTER TABLE `Students` CHANGE `Grade#Final#Exam` `GradeFinalExam` DOUBLE;

Upvotes: 0

Pavel Zimogorov
Pavel Zimogorov

Reputation: 1442

Use quotes around the column name :

ALTER TABLE Students CHANGE 'Grade#Final#Exam' DOUBLE;

Upvotes: 0

James Scott
James Scott

Reputation: 1074

Use backticks:

CREATE TABLE test (`Grade#Final#Exam` DOUBLE);

DESCRIBE test;

ALTER TABLE test CHANGE `Grade#Final#Exam` GradeFinalExam DOUBLE;

DESCRIBE test;

Regards,

James

Upvotes: 1

Marc Zaharescu
Marc Zaharescu

Reputation: 639

The general syntax for renaming a column in MySQL is ALTER TABLE "name of table" CHANGE "name of old column" "name of new column" "Datatype";

Try to escape the column names with backticks not with single quotes

Upvotes: 0

Related Questions