CODEWITHSUNDEEP
mysqlspecial-characterscase-sensitive
Mark Henry
Mark Henry

Reputation: 2699

Replace space with underscore in table

How can I write a SQL query to replace all occurrences of space in a table with underscore and set all characters to lowercase?

Upvotes: 4

Views: 27361

Answers (2)

Nsengiyunva
Nsengiyunva

Reputation: 21

When using the UPDATE statement in SQL, always remember to include a WHERE clause -- so says MYSQL Workbench! :D

My Answer though:

REPLACE(string1, find_in_string, replacementValue);

Upvotes: 0

newfurniturey
newfurniturey

Reputation: 38436

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.

Upvotes: 22

Related Questions