Replace space with underscore in table
14,628
To update a single column in a single table, you can use a combination of LOWER()
and REPLACE()
:
UPDATE table_name SET column_name=LOWER(REPLACE(column_name, ' ', '_'))
To "duplicate" the existing column, and perform the updates on the duplicate (per your question in a comment), you can use MySQL's ALTER
command before the UPDATE
query:
ALTER TABLE table_name ADD duplicate_column_name VARCHAR(255) AFTER column_name;
UPDATE table_name SET duplicate_column_name = LOWER(REPLACE(column_name, ' ', '_'));
Just be sure to update the data-type in the ALTER
command to reflect your actual data-type.
Comments
-
Mark Henry almost 2 years
How can I write a SQL query to replace all occurrences of space in a table with underscore and set all characters to lowercase?
-
mlishn over 11 yearswould this work for the entire table in one query? Wouldn't be need to iterate through each row to get this working?
-
Mark Henry over 11 yearsfirst I need to duplicate the column. How do I do that?
-
newfurniturey over 11 years@mlishn This will work for an entire column in the given table; If he needs multiple columns in the same table he could append them to the update clause. If he needs multiple tables, he could use either multiple
UPDATE
clauses - or write a stored procedure that iterates throughinformation_schema
. -
mlishn over 11 years@newfurniturey great answer then :)
-
newfurniturey over 11 years@mark I've updated my answer to show how to duplicate the column first.