CODEWITHSUNDEEP
mysqlsql
zach
zach

Reputation: 478

SQL copy one column then paste it in a new column with all white space removed

I want to copy one column in my database and paste it into another column with all white space removed. I do not want to use export/import but a SQL query if this is possible.

Upvotes: 1

Views: 1618

Answers (3)

Abhay
Abhay

Reputation: 6645

Add the new column city2 using ALTER TABLE. And then run the following query:

update `table` set `city2` = replace(`city`, ' ', '');

However, the above is assuming that both columns are in the same table. If they are from different tables you may use JOINS in the update query. Let me know if that helps.

Upvotes: 1

p.campbell
p.campbell

Reputation: 100607

Try this

UPDATE MyTable
SET City2 = REPLACE(City, ' ', '');

This will remove all whitespace. Leading spaces, trailing spaces, all spaces.

This will turn:

  • "Rio De Janerio" into "RioDeJanerio"
  • "Sandusky " into "Sandusky"
  • " Orangeville" into "Orangeville"

Upvotes: 4

Bassetassen
Bassetassen

Reputation: 21814

If the whitespace is leading and trailing then use the TRIM function. If it all whitespaces beetween words, trailing and leading use the replace function that @p.campbell suggested

Upvotes: 1

Related Questions